DROP TABLE IF EXISTS `macro_ipminitalETL_macro_loadProductMapping_Names1_0_0`; CREATE EXTERNAL TABLE `macro_ipminitalETL_macro_loadProductMapping_Names1_0_0` (`GlassProductName` STRING, `Finance` STRING, `Software` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/gadmin/glaas/input/alf/rework/AllFinanceSoftwareGlassProductName'; DROP TABLE IF EXISTS `macro_ipminitalETL_QTRipm_0`; CREATE EXTERNAL TABLE `macro_ipminitalETL_QTRipm_0` (`Qtr` STRING, `Year` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/gadmin/glaas/input/common/QtrIPM.csv'; DROP TABLE IF EXISTS `macro_ipminitalETL_macro_gportalETL_A_0_0`; CREATE EXTERNAL TABLE `macro_ipminitalETL_macro_gportalETL_A_0_0` (`ID` STRING, `Stage` STRING, `Product` STRING, `Version` STRING, `Codename` STRING, `Vendor` STRING, `Language` STRING, `Deliverable` STRING, `Label` STRING, `DeliveryItem` STRING, `Unit` STRING, `Rate` STRING, `PendingQty` STRING, `ApprovedQty` STRING, `InitialEstimate` STRING, `ApprovedQuotes` STRING, `TotalInvoices` STRING, `PendingQuotes` STRING, `InvoiceNumber` STRING, `IPM` STRING, `PMBU` STRING, `PMBUName` STRING, `BU_NAME` STRING, `ProfitCenter` STRING, `CoreCostCenter` STRING, `COGSNONCOGS` STRING, `PONumber` STRING, `POAmount` STRING, `ShipDate` STRING, `InvoiceDate` STRING, `ProjectCreated` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LOCATION '/user/gadmin/glaas/input/gportal/dev/all_data_dump_detail.csv'; DROP TABLE IF EXISTS `macro_ipminitalETL_macro_getLocaleCodes_A1_0_0`; CREATE EXTERNAL TABLE `macro_ipminitalETL_macro_getLocaleCodes_A1_0_0` (`Lang` STRING, `code` STRING, `tier` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LOCATION '/user/gadmin/glaas/input/common/LocaleCodes.csv'; DROP TABLE IF EXISTS `macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0`; CREATE EXTERNAL TABLE `macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0` (`year` STRING, `quarter` STRING, `startMonthName` STRING, `startMonth` STRING, `startDay` STRING, `startYear` STRING, `startDayOfWeek` STRING, `endMonthName` STRING, `endMonth` STRING, `endDay` STRING, `endYear` STRING, `endDayOfWeek` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LOCATION '/user/gadmin/glaas/input/common/Qtr.csv'; EXPLAIN INSERT OVERWRITE DIRECTORY '/user/gadmin/glaas/test/data/output/test/dev/glaas_gportal_ipm_cost_daily' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' (SELECT `t85`.`code`, `t85`.`SoftwareProductName` AS `softwareproductname`, `t85`.`GportalProductName` AS `gportalproductname`, `t85`.`GlassProductName` AS `glassproductname`, `t85`.`Id` AS `id`, `t85`.`VendorId` AS `vendorid`, `t85`.`Label` AS `label`, `t85`.`InvoiceDateNew` AS `invoicedatenew`, `t85`.`IPM` AS `ipm`, `t85`.`InvoiceDateDateTime` AS `invoicedatedatetime`, `t85`.`ProductVersion` AS `productversion`, `t85`.`Product` AS `product`, `t85`.`Version` AS `version`, `t85`.`Vendor` AS `vendor`, `t85`.`Language` AS `language`, `t85`.`Deliverable` AS `deliverable`, `t85`.`DeliveryItem` AS `deliveryitem`, `t85`.`BUName` AS `buname`, `t85`.`PMBUName` AS `pmbuname`, `t85`.`COGSNONCOGS` AS `cogsnoncogs`, `t85`.`TotalInvoices` AS `invoice`, `t85`.`Year` AS `year`, `t85`.`Qtr` AS `qtr`, `t85`.`Date` AS `date` FROM (SELECT `t83`.`code`, `t81`.`SoftwareProductName`, `t81`.`GportalProductName`, `t81`.`GlassProductName`, `t81`.`Id`, CAST(`t81`.`Product` || `t81`.`Language` || `t81`.`Vendor` AS STRING) AS `VendorId`, `t81`.`Label`, `t81`.`InvoiceDateNew`, `t81`.`IPM`, `t81`.`InvoiceDateDateTime`, `t81`.`ProductVersion`, `t81`.`Product`, `t81`.`Version`, `t81`.`Vendor`, `t81`.`Language`, `t81`.`Deliverable`, `t81`.`DeliveryItem`, `t81`.`BUName`, `t81`.`BUName` AS `PMBUName`, `t81`.`COGSNONCOGS`, `t81`.`Invoice` AS `TotalInvoices`, `t81`.`Year`, `t81`.`Qtr`, `t81`.`Date` FROM (SELECT `t80`.`Finance` AS `GportalProductName`, `t80`.`Software` AS `SoftwareProductName`, `t80`.`GlassProductName`, `t78`.`Id`, CAST(`t78`.`Id` AS STRING) AS `VendorId`, `t78`.`Label`, `t78`.`InvoiceDateNew`, `t78`.`IPM`, `t78`.`InvoiceDateDateTime`, `t78`.`ProductVersion`, `t78`.`Product`, `t78`.`Version`, `t78`.`Vendor`, `t78`.`Language`, `t78`.`Deliverable`, `t78`.`DeliveryItem`, `t78`.`BUName`, `t78`.`BUName` AS `PMBUName`, `t78`.`COGSNONCOGS`, `t78`.`Invoice`, `t78`.`Year`, `t78`.`Qtr`, `t78`.`Date` FROM (SELECT `t75`.`Id`, `t75`.`VendorId`, `t75`.`Label`, `t75`.`InvoiceDateNew`, `t75`.`IPM`, `t75`.`InvoiceDateDateTime`, `t75`.`ProductVersion`, `t75`.`Product`, `t75`.`Version`, `t75`.`Vendor`, `t75`.`Language`, `t75`.`Deliverable`, `t75`.`DeliveryItem`, `t75`.`BUName`, `t75`.`PMBUName`, `t75`.`COGSNONCOGS`, `t75`.`Invoice`, `t75`.`Year`, `t75`.`Qtr`, `t75`.`Date` FROM (SELECT `ID` AS `Id`, `VendorId`, `Label`, `InvoiceDateNew`, `IPM`, ToDate(`InvoiceDateNew`, 'yyyyMMdd') AS `InvoiceDateDateTime`, `ProductVersion`, `Product`, `Version`, `Vendor`, UPPER(CASE WHEN CASE WHEN PIG_SIZE(`Language`) > 15 THEN SUBSTRING(`Language`, 0, 15) ELSE `Language` END = 'NORWEGIAN (BOKM' THEN 'NORWEGIAN (BOKMAL)' ELSE `Language` END) AS `Language`, `Deliverable`, `DeliveryItem`, `BUName`, `PMBUName`, `COGSNONCOGS`, `TotalInvoices` AS `Invoice`, CAST(`Year` AS INTEGER) AS `Year`, `Qtr`, `Date`, CAST(`Qtr` || CAST(CAST(`Year` AS INTEGER) AS STRING) AS STRING) AS `DateYear` FROM (SELECT * FROM (SELECT `t70`.`id` AS `ID`, `t70`.`D::Month` AS `Month`, `t70`.`invoicedate` AS `InvoiceDate`, `t70`.`invoicedatenew` AS `InvoiceDateNew`, `t70`.`unit` AS `Unit`, `t70`.`productversion` AS `ProductVersion`, `t70`.`label` AS `Label`, `t70`.`invoicenumber` AS `InvoiceNumber`, `t70`.`product` AS `Product`, CAST(`t70`.`approvedqty` AS FLOAT) AS `ApprovedQty`, `t70`.`version` AS `Version`, `t70`.`vendor` AS `Vendor`, `t70`.`language` AS `Language`, `t70`.`deliverable` AS `Deliverable`, `t70`.`deliveryitem` AS `DeliveryItem`, `t70`.`buname` AS `BUName`, `t70`.`pmbuname` AS `PMBUName`, `t70`.`cogsnoncogs` AS `COGSNONCOGS`, `t70`.`invoice` AS `TotalInvoices`, `t70`.`year` AS `Year`, `t70`.`qtr` AS `Qtr`, `t70`.`date` AS `Date`, `t70`.`ipm` AS `IPM`, CAST(CAST(`t70`.`year` AS STRING) || '-' || CAST(`t70`.`D::Month` AS STRING) AS STRING) AS `DateYear`, `t70`.`D::SecondPreviousYear` AS `SecondPreviousYear`, `t70`.`VendorId`, `t70`.`Codename`, `t70`.`Rate`, CAST(`t70`.`product` || `t70`.`version` || `t70`.`language` || `t70`.`deliverable` || `t70`.`deliveryitem` || `t70`.`unit` || `t70`.`vendor` AS STRING) AS `productvendorid`, `t70`.`YearPart`, CAST(`t70`.`product` || `t70`.`version` || `t70`.`language` || `t70`.`deliverable` || `t70`.`deliveryitem` || `t70`.`unit` || `t70`.`vendor` || `t70`.`year` || `t70`.`YearPart` AS STRING) AS `productVendorbinannual`, CAST(`t70`.`product` || `t70`.`year` AS STRING) AS `prodyear` FROM (SELECT `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::ID` AS `id`, `t68`.`Month` AS `D::Month`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Date` AS `invoicedate`, CAST(`t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDateNew` AS STRING) AS `invoicedatenew`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Unit` AS `unit`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::ProductVersion` AS `productversion`, `t68`.`Year` AS `D::SecondPreviousYear`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label` AS `label`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceNumber` AS `invoicenumber`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Product` AS `product`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::ApprovedQty` AS `approvedqty`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Version` AS `version`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Vendor` AS `vendor`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Language` AS `language`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable` AS `deliverable`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem` AS `deliveryitem`, `t68`.`BUName` AS `buname`, `t68`.`BUName` AS `pmbuname`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::COGSNONCOGS` AS `cogsnoncogs`, `t37`.`TotalSummedInvoice` AS `invoice`, `t68`.`Year` AS `year`, `t68`.`macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr` AS `qtr`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Date` AS `date`, `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::IPM` AS `ipm`, CAST(CAST(`t68`.`Year` AS STRING) || '-' || CAST(`t68`.`Month` AS STRING) AS STRING) AS `DateYear`, CAST(`t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Product` || `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Language` || `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Vendor` AS STRING) AS `VendorId`, `t68`.`Codename`, `t68`.`Rate`, CASE WHEN `t68`.`macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr` = 'Q1' OR `t68`.`macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr` = 'Q2' THEN 'H1' ELSE 'H2' END AS `YearPart`, `t68`.`rawyear`, CASE WHEN `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable` = 'Quality Engineering/Testing' AND (`t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label` LIKE '(?i).*Functional testing.*' OR `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem` LIKE '(?i).*Functional testing.*') THEN 'Functional Testing' ELSE CASE WHEN `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable` = 'Quality Engineering/Testing' AND (`t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label` LIKE '(?i).*Linguistic testing.*' OR `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem` LIKE '(?i).*Linguistic testing.*' OR `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem` LIKE '(?i).*UI/ling.*' OR `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem` LIKE '(?i).*New Feature*') THEN 'Linguistic Testing' ELSE CASE WHEN `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable` = 'Quality Engineering/Testing' THEN 'Functional Testing' ELSE `t68`.`macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable` END END END AS `NewDeliverable` FROM (SELECT `t36`.`D`, PIG_SUM(PIG_BAG(MULTISET_PROJECTION(`t19`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`, 13))) AS `TotalSummedInvoice` FROM (SELECT `t13`.`ID` AS `group`, COLLECT_LIST(STRUCT(`t16`.`year`, 2021 - 1, `t16`.`Qtr`, `t13`.`Date`, `t13`.`ID`, `t13`.`InvoiceDateNew`, `t13`.`ProductVersion`, `t13`.`Product`, `t13`.`Month`, `t13`.`Version`, `t13`.`Vendor`, `t13`.`Language`, `t13`.`Deliverable`, `t13`.`TotalInvoices`, `t13`.`Label`, `t13`.`Date`, `t13`.`InvoiceNumber`, `t13`.`DeliveryItem`, `t13`.`Unit`, `t13`.`ApprovedQty`, CASE WHEN `t13`.`BUName` = 'Digital Media' THEN 'DMe' ELSE CASE WHEN `t13`.`BUName` = 'Consumer' THEN 'DMe' ELSE CASE WHEN `t13`.`BUName` = 'Digital Marketing' THEN 'DMa' ELSE CASE WHEN `t13`.`BUName` = 'Document Cloud' THEN 'DC' ELSE CASE WHEN `t13`.`BUName` = 'Print & Publishing' THEN 'PPBU' ELSE `t13`.`BUName` END END END END END, `t13`.`BUName`, `t13`.`COGSNONCOGS`, `t13`.`IPM`, `t13`.`Codename`, CAST(`t13`.`Rate` AS DOUBLE), `t13`.`Year`)) AS `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) AS `Year`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) AS `Month`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS `Date`, REPLACE(CAST(CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) || `ProductVersion` || `Vendor` || `Language` || `Deliverable` || `DeliveryItem` || `InvoiceNumber` || `Label` AS STRING), ' ', '') AS `ID`, CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) AS `InvoiceDateNew`, `ProductVersion`, UPPER(`Product`) AS `Product`, `Version`, `InvoiceNumber`, `InvoiceDate`, `Vendor`, `Label`, `Language`, `Deliverable`, `Unit`, `ApprovedQty`, CAST(`TotalInvoices` AS FLOAT) AS `TotalInvoices`, `DeliveryItem`, `BUName`, `PMBUName`, `COGSNONCOGS`, `IPM`, `Codename`, `Rate` FROM (SELECT * FROM (SELECT * FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0`, TRIM(REPLACE(CAST(REPLACE(`Product`, '"', '') || ' ' || REPLACE(`Version`, '"', '') AS STRING), ',', '.')) AS `ProductVersion`, TRIM(REPLACE(REPLACE(`Product`, '"', ''), ',', '.')) AS `Product`, TRIM(REPLACE(REPLACE(`Version`, '"', ''), ',', '.')) AS `Version`, TRIM(REPLACE(REPLACE(`Vendor`, '"', ''), ',', '.')) AS `Vendor`, TRIM(REPLACE(REPLACE(`Language`, '"', ''), ',', '.')) AS `Language`, TRIM(REPLACE(REPLACE(`Deliverable`, '"', ''), ',', '.')) AS `Deliverable`, TRIM(REPLACE(REPLACE(`Label`, '"', ''), ',', '.')) AS `Label`, TRIM(REPLACE(REPLACE(`TotalInvoices`, '"', ''), '\u0024', '')) AS `TotalInvoices`, TRIM(REPLACE(REPLACE(`Unit`, '"', ''), ',', '.')) AS `Unit`, TRIM(REPLACE(`InvoiceDate`, '"', '')) AS `InvoiceDate`, `ApprovedQty`, TRIM(REPLACE(REPLACE(`DeliveryItem`, '"', ''), ',', '.')) AS `DeliveryItem`, TRIM(REPLACE(REPLACE(`BU_NAME`, '"', ''), ',', '.')) AS `BUName`, TRIM(REPLACE(REPLACE(`PMBUName`, '"', ''), ',', '.')) AS `PMBUName`, TRIM(REPLACE(REPLACE(`COGSNONCOGS`, '"', ''), ',', '.')) AS `COGSNONCOGS`, TRIM(REPLACE(REPLACE(`InvoiceNumber`, '"', ''), ',', '.')) AS `InvoiceNumber`, TRIM(REPLACE(REPLACE(`IPM`, '"', ''), ',', '.')) AS `IPM`, TRIM(REPLACE(REPLACE(`Codename`, '"', ''), ',', '.')) AS `Codename`, TRIM(REPLACE(REPLACE(`Rate`, '"', ''), '\u0024', '')) AS `Rate` FROM macro_ipminitalETL_macro_gportalETL_A_0_0) AS `t9` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0` > 1) AS `t10` WHERE `InvoiceDate` <> '') AS `t11` WHERE CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) <> '1970') AS `t13` INNER JOIN (SELECT `startMonth` || '/' || `startDay` || '/' || `startYear` AS `startdate`, `startDay` || '/' || `startMonth` || '/' || `startYear` AS `startdaterework`, `endMonth` || '/' || `endDay` || '/' || `endYear` AS `endate`, `endDay` || '/' || `endMonth` || '/' || `endYear` AS `endaterework`, `year`, `quarter` AS `Qtr`, `startMonthName` AS `MonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0`, `year`, `quarter`, `startMonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0) AS `t14` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0` > 1) AS `t16` ON `t13`.`InvoiceDate` = `t16`.`startdate` GROUP BY `t13`.`ID`) AS `t19` INNER JOIN (SELECT COLLECT_LIST(STRUCT(`Year`, `SecondPreviousYear`, `macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Date`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ID`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDateNew`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ProductVersion`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Product`, `Month`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Version`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Vendor`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Language`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::TotalInvoices`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDate`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceNumber`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Unit`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ApprovedQty`, `BUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::PMBUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::COGSNONCOGS`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::IPM`, `Codename`, `Rate`, `rawyear`, `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`)) AS `D`, `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT * FROM (SELECT `t30`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT COLLECT_LIST(STRUCT(`t27`.`year`, 2021 - 1, `t27`.`Qtr`, `t24`.`Date`, `t24`.`ID`, `t24`.`InvoiceDateNew`, `t24`.`ProductVersion`, `t24`.`Product`, `t24`.`Month`, `t24`.`Version`, `t24`.`Vendor`, `t24`.`Language`, `t24`.`Deliverable`, `t24`.`TotalInvoices`, `t24`.`Label`, `t24`.`Date`, `t24`.`InvoiceNumber`, `t24`.`DeliveryItem`, `t24`.`Unit`, `t24`.`ApprovedQty`, CASE WHEN `t24`.`BUName` = 'Digital Media' THEN 'DMe' ELSE CASE WHEN `t24`.`BUName` = 'Consumer' THEN 'DMe' ELSE CASE WHEN `t24`.`BUName` = 'Digital Marketing' THEN 'DMa' ELSE CASE WHEN `t24`.`BUName` = 'Document Cloud' THEN 'DC' ELSE CASE WHEN `t24`.`BUName` = 'Print & Publishing' THEN 'PPBU' ELSE `t24`.`BUName` END END END END END, `t24`.`BUName`, `t24`.`COGSNONCOGS`, `t24`.`IPM`, `t24`.`Codename`, CAST(`t24`.`Rate` AS DOUBLE), `t24`.`Year`)) AS `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) AS `Year`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) AS `Month`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS `Date`, REPLACE(CAST(CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) || `ProductVersion` || `Vendor` || `Language` || `Deliverable` || `DeliveryItem` || `InvoiceNumber` || `Label` AS STRING), ' ', '') AS `ID`, CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) AS `InvoiceDateNew`, `ProductVersion`, UPPER(`Product`) AS `Product`, `Version`, `InvoiceNumber`, `InvoiceDate`, `Vendor`, `Label`, `Language`, `Deliverable`, `Unit`, `ApprovedQty`, CAST(`TotalInvoices` AS FLOAT) AS `TotalInvoices`, `DeliveryItem`, `BUName`, `PMBUName`, `COGSNONCOGS`, `IPM`, `Codename`, `Rate` FROM (SELECT * FROM (SELECT * FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0`, TRIM(REPLACE(CAST(REPLACE(`Product`, '"', '') || ' ' || REPLACE(`Version`, '"', '') AS STRING), ',', '.')) AS `ProductVersion`, TRIM(REPLACE(REPLACE(`Product`, '"', ''), ',', '.')) AS `Product`, TRIM(REPLACE(REPLACE(`Version`, '"', ''), ',', '.')) AS `Version`, TRIM(REPLACE(REPLACE(`Vendor`, '"', ''), ',', '.')) AS `Vendor`, TRIM(REPLACE(REPLACE(`Language`, '"', ''), ',', '.')) AS `Language`, TRIM(REPLACE(REPLACE(`Deliverable`, '"', ''), ',', '.')) AS `Deliverable`, TRIM(REPLACE(REPLACE(`Label`, '"', ''), ',', '.')) AS `Label`, TRIM(REPLACE(REPLACE(`TotalInvoices`, '"', ''), '\u0024', '')) AS `TotalInvoices`, TRIM(REPLACE(REPLACE(`Unit`, '"', ''), ',', '.')) AS `Unit`, TRIM(REPLACE(`InvoiceDate`, '"', '')) AS `InvoiceDate`, `ApprovedQty`, TRIM(REPLACE(REPLACE(`DeliveryItem`, '"', ''), ',', '.')) AS `DeliveryItem`, TRIM(REPLACE(REPLACE(`BU_NAME`, '"', ''), ',', '.')) AS `BUName`, TRIM(REPLACE(REPLACE(`PMBUName`, '"', ''), ',', '.')) AS `PMBUName`, TRIM(REPLACE(REPLACE(`COGSNONCOGS`, '"', ''), ',', '.')) AS `COGSNONCOGS`, TRIM(REPLACE(REPLACE(`InvoiceNumber`, '"', ''), ',', '.')) AS `InvoiceNumber`, TRIM(REPLACE(REPLACE(`IPM`, '"', ''), ',', '.')) AS `IPM`, TRIM(REPLACE(REPLACE(`Codename`, '"', ''), ',', '.')) AS `Codename`, TRIM(REPLACE(REPLACE(`Rate`, '"', ''), '\u0024', '')) AS `Rate` FROM macro_ipminitalETL_macro_gportalETL_A_0_0) AS `t20` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0` > 1) AS `t21` WHERE `InvoiceDate` <> '') AS `t22` WHERE CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) <> '1970') AS `t24` INNER JOIN (SELECT `startMonth` || '/' || `startDay` || '/' || `startYear` AS `startdate`, `startDay` || '/' || `startMonth` || '/' || `startYear` AS `startdaterework`, `endMonth` || '/' || `endDay` || '/' || `endYear` AS `endate`, `endDay` || '/' || `endMonth` || '/' || `endYear` AS `endaterework`, `year`, `quarter` AS `Qtr`, `startMonthName` AS `MonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0`, `year`, `quarter`, `startMonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0) AS `t25` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0` > 1) AS `t27` ON `t24`.`InvoiceDate` = `t27`.`startdate` GROUP BY `t24`.`ID`) AS `t30` GROUP BY `t30`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`) `t31` LATERAL VIEW INLINE (`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`) `t31` AS `Year`, `SecondPreviousYear`, `macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Date`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ID`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDateNew`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ProductVersion`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Product`, `Month`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Version`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Vendor`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Language`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::TotalInvoices`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDate`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceNumber`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Unit`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ApprovedQty`, `BUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::PMBUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::COGSNONCOGS`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::IPM`, `Codename`, `Rate`, `rawyear` LIMIT 1) AS `t33` GROUP BY `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`) AS `t36` ON `t19`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` = `t36`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`) AS `t37` INNER JOIN (SELECT * FROM (SELECT `t65`.`D` FROM (SELECT `t42`.`ID` AS `group`, COLLECT_LIST(STRUCT(`t45`.`year`, 2021 - 1, `t45`.`Qtr`, `t42`.`Date`, `t42`.`ID`, `t42`.`InvoiceDateNew`, `t42`.`ProductVersion`, `t42`.`Product`, `t42`.`Month`, `t42`.`Version`, `t42`.`Vendor`, `t42`.`Language`, `t42`.`Deliverable`, `t42`.`TotalInvoices`, `t42`.`Label`, `t42`.`Date`, `t42`.`InvoiceNumber`, `t42`.`DeliveryItem`, `t42`.`Unit`, `t42`.`ApprovedQty`, CASE WHEN `t42`.`BUName` = 'Digital Media' THEN 'DMe' ELSE CASE WHEN `t42`.`BUName` = 'Consumer' THEN 'DMe' ELSE CASE WHEN `t42`.`BUName` = 'Digital Marketing' THEN 'DMa' ELSE CASE WHEN `t42`.`BUName` = 'Document Cloud' THEN 'DC' ELSE CASE WHEN `t42`.`BUName` = 'Print & Publishing' THEN 'PPBU' ELSE `t42`.`BUName` END END END END END, `t42`.`BUName`, `t42`.`COGSNONCOGS`, `t42`.`IPM`, `t42`.`Codename`, CAST(`t42`.`Rate` AS DOUBLE), `t42`.`Year`)) AS `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) AS `Year`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) AS `Month`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS `Date`, REPLACE(CAST(CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) || `ProductVersion` || `Vendor` || `Language` || `Deliverable` || `DeliveryItem` || `InvoiceNumber` || `Label` AS STRING), ' ', '') AS `ID`, CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) AS `InvoiceDateNew`, `ProductVersion`, UPPER(`Product`) AS `Product`, `Version`, `InvoiceNumber`, `InvoiceDate`, `Vendor`, `Label`, `Language`, `Deliverable`, `Unit`, `ApprovedQty`, CAST(`TotalInvoices` AS FLOAT) AS `TotalInvoices`, `DeliveryItem`, `BUName`, `PMBUName`, `COGSNONCOGS`, `IPM`, `Codename`, `Rate` FROM (SELECT * FROM (SELECT * FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0`, TRIM(REPLACE(CAST(REPLACE(`Product`, '"', '') || ' ' || REPLACE(`Version`, '"', '') AS STRING), ',', '.')) AS `ProductVersion`, TRIM(REPLACE(REPLACE(`Product`, '"', ''), ',', '.')) AS `Product`, TRIM(REPLACE(REPLACE(`Version`, '"', ''), ',', '.')) AS `Version`, TRIM(REPLACE(REPLACE(`Vendor`, '"', ''), ',', '.')) AS `Vendor`, TRIM(REPLACE(REPLACE(`Language`, '"', ''), ',', '.')) AS `Language`, TRIM(REPLACE(REPLACE(`Deliverable`, '"', ''), ',', '.')) AS `Deliverable`, TRIM(REPLACE(REPLACE(`Label`, '"', ''), ',', '.')) AS `Label`, TRIM(REPLACE(REPLACE(`TotalInvoices`, '"', ''), '\u0024', '')) AS `TotalInvoices`, TRIM(REPLACE(REPLACE(`Unit`, '"', ''), ',', '.')) AS `Unit`, TRIM(REPLACE(`InvoiceDate`, '"', '')) AS `InvoiceDate`, `ApprovedQty`, TRIM(REPLACE(REPLACE(`DeliveryItem`, '"', ''), ',', '.')) AS `DeliveryItem`, TRIM(REPLACE(REPLACE(`BU_NAME`, '"', ''), ',', '.')) AS `BUName`, TRIM(REPLACE(REPLACE(`PMBUName`, '"', ''), ',', '.')) AS `PMBUName`, TRIM(REPLACE(REPLACE(`COGSNONCOGS`, '"', ''), ',', '.')) AS `COGSNONCOGS`, TRIM(REPLACE(REPLACE(`InvoiceNumber`, '"', ''), ',', '.')) AS `InvoiceNumber`, TRIM(REPLACE(REPLACE(`IPM`, '"', ''), ',', '.')) AS `IPM`, TRIM(REPLACE(REPLACE(`Codename`, '"', ''), ',', '.')) AS `Codename`, TRIM(REPLACE(REPLACE(`Rate`, '"', ''), '\u0024', '')) AS `Rate` FROM macro_ipminitalETL_macro_gportalETL_A_0_0) AS `t38` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0` > 1) AS `t39` WHERE `InvoiceDate` <> '') AS `t40` WHERE CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) <> '1970') AS `t42` INNER JOIN (SELECT `startMonth` || '/' || `startDay` || '/' || `startYear` AS `startdate`, `startDay` || '/' || `startMonth` || '/' || `startYear` AS `startdaterework`, `endMonth` || '/' || `endDay` || '/' || `endYear` AS `endate`, `endDay` || '/' || `endMonth` || '/' || `endYear` AS `endaterework`, `year`, `quarter` AS `Qtr`, `startMonthName` AS `MonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0`, `year`, `quarter`, `startMonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0) AS `t43` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0` > 1) AS `t45` ON `t42`.`InvoiceDate` = `t45`.`startdate` GROUP BY `t42`.`ID`) AS `t48` INNER JOIN (SELECT COLLECT_LIST(STRUCT(`Year`, `SecondPreviousYear`, `macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Date`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ID`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDateNew`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ProductVersion`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Product`, `Month`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Version`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Vendor`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Language`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::TotalInvoices`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDate`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceNumber`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Unit`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ApprovedQty`, `BUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::PMBUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::COGSNONCOGS`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::IPM`, `Codename`, `Rate`, `rawyear`, `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`)) AS `D`, `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT * FROM (SELECT `t59`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT COLLECT_LIST(STRUCT(`t56`.`year`, 2021 - 1, `t56`.`Qtr`, `t53`.`Date`, `t53`.`ID`, `t53`.`InvoiceDateNew`, `t53`.`ProductVersion`, `t53`.`Product`, `t53`.`Month`, `t53`.`Version`, `t53`.`Vendor`, `t53`.`Language`, `t53`.`Deliverable`, `t53`.`TotalInvoices`, `t53`.`Label`, `t53`.`Date`, `t53`.`InvoiceNumber`, `t53`.`DeliveryItem`, `t53`.`Unit`, `t53`.`ApprovedQty`, CASE WHEN `t53`.`BUName` = 'Digital Media' THEN 'DMe' ELSE CASE WHEN `t53`.`BUName` = 'Consumer' THEN 'DMe' ELSE CASE WHEN `t53`.`BUName` = 'Digital Marketing' THEN 'DMa' ELSE CASE WHEN `t53`.`BUName` = 'Document Cloud' THEN 'DC' ELSE CASE WHEN `t53`.`BUName` = 'Print & Publishing' THEN 'PPBU' ELSE `t53`.`BUName` END END END END END, `t53`.`BUName`, `t53`.`COGSNONCOGS`, `t53`.`IPM`, `t53`.`Codename`, CAST(`t53`.`Rate` AS DOUBLE), `t53`.`Year`)) AS `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` FROM (SELECT CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) AS `Year`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) AS `Month`, CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS `Date`, REPLACE(CAST(CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) || `ProductVersion` || `Vendor` || `Language` || `Deliverable` || `DeliveryItem` || `InvoiceNumber` || `Label` AS STRING), ' ', '') AS `ID`, CAST(CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[0] AS STRING) || CAST(STRSPLIT(`InvoiceDate`, '/', 3)[1] AS STRING) AS STRING) AS `InvoiceDateNew`, `ProductVersion`, UPPER(`Product`) AS `Product`, `Version`, `InvoiceNumber`, `InvoiceDate`, `Vendor`, `Label`, `Language`, `Deliverable`, `Unit`, `ApprovedQty`, CAST(`TotalInvoices` AS FLOAT) AS `TotalInvoices`, `DeliveryItem`, `BUName`, `PMBUName`, `COGSNONCOGS`, `IPM`, `Codename`, `Rate` FROM (SELECT * FROM (SELECT * FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0`, TRIM(REPLACE(CAST(REPLACE(`Product`, '"', '') || ' ' || REPLACE(`Version`, '"', '') AS STRING), ',', '.')) AS `ProductVersion`, TRIM(REPLACE(REPLACE(`Product`, '"', ''), ',', '.')) AS `Product`, TRIM(REPLACE(REPLACE(`Version`, '"', ''), ',', '.')) AS `Version`, TRIM(REPLACE(REPLACE(`Vendor`, '"', ''), ',', '.')) AS `Vendor`, TRIM(REPLACE(REPLACE(`Language`, '"', ''), ',', '.')) AS `Language`, TRIM(REPLACE(REPLACE(`Deliverable`, '"', ''), ',', '.')) AS `Deliverable`, TRIM(REPLACE(REPLACE(`Label`, '"', ''), ',', '.')) AS `Label`, TRIM(REPLACE(REPLACE(`TotalInvoices`, '"', ''), '\u0024', '')) AS `TotalInvoices`, TRIM(REPLACE(REPLACE(`Unit`, '"', ''), ',', '.')) AS `Unit`, TRIM(REPLACE(`InvoiceDate`, '"', '')) AS `InvoiceDate`, `ApprovedQty`, TRIM(REPLACE(REPLACE(`DeliveryItem`, '"', ''), ',', '.')) AS `DeliveryItem`, TRIM(REPLACE(REPLACE(`BU_NAME`, '"', ''), ',', '.')) AS `BUName`, TRIM(REPLACE(REPLACE(`PMBUName`, '"', ''), ',', '.')) AS `PMBUName`, TRIM(REPLACE(REPLACE(`COGSNONCOGS`, '"', ''), ',', '.')) AS `COGSNONCOGS`, TRIM(REPLACE(REPLACE(`InvoiceNumber`, '"', ''), ',', '.')) AS `InvoiceNumber`, TRIM(REPLACE(REPLACE(`IPM`, '"', ''), ',', '.')) AS `IPM`, TRIM(REPLACE(REPLACE(`Codename`, '"', ''), ',', '.')) AS `Codename`, TRIM(REPLACE(REPLACE(`Rate`, '"', ''), '\u0024', '')) AS `Rate` FROM macro_ipminitalETL_macro_gportalETL_A_0_0) AS `t49` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Trimmed_0_0` > 1) AS `t50` WHERE `InvoiceDate` <> '') AS `t51` WHERE CAST(STRSPLIT(`InvoiceDate`, '/', 3)[2] AS STRING) <> '1970') AS `t53` INNER JOIN (SELECT `startMonth` || '/' || `startDay` || '/' || `startYear` AS `startdate`, `startDay` || '/' || `startMonth` || '/' || `startYear` AS `startdaterework`, `endMonth` || '/' || `endDay` || '/' || `endYear` AS `endate`, `endDay` || '/' || `endMonth` || '/' || `endYear` AS `endaterework`, `year`, `quarter` AS `Qtr`, `startMonthName` AS `MonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM (SELECT RANK() OVER () AS `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0`, `year`, `quarter`, `startMonthName`, `startMonth`, `startDay`, `startYear`, `startDayOfWeek`, `endMonthName`, `endMonth`, `endDay`, `endYear`, `endDayOfWeek` FROM macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0) AS `t54` WHERE `rank_macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0` > 1) AS `t56` ON `t53`.`InvoiceDate` = `t56`.`startdate` GROUP BY `t53`.`ID`) AS `t59` GROUP BY `t59`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`) `t60` LATERAL VIEW INLINE (`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`) `t60` AS `Year`, `SecondPreviousYear`, `macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Date`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ID`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDateNew`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ProductVersion`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Product`, `Month`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Version`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Vendor`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Language`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::TotalInvoices`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDate`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceNumber`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Unit`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ApprovedQty`, `BUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::PMBUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::COGSNONCOGS`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::IPM`, `Codename`, `Rate`, `rawyear` LIMIT 1) AS `t62` GROUP BY `macro_ipminitalETL_macro_gportalETL_YearFixed_0_0`) AS `t65` ON `t48`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` = `t65`.`macro_ipminitalETL_macro_gportalETL_YearFixed_0_0` GROUP BY `t65`.`D`) `t67` LATERAL VIEW INLINE (`D`) `t67` AS `Year`, `SecondPreviousYear`, `macro_ipminitalETL_macro_gportalETL_Qtrinfo_0_0::Qtr`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Date`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ID`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDateNew`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ProductVersion`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Product`, `Month`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Version`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Vendor`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Language`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Deliverable`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::TotalInvoices`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Label`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceDate`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::InvoiceNumber`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::DeliveryItem`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::Unit`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::ApprovedQty`, `BUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::PMBUName`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::COGSNONCOGS`, `macro_ipminitalETL_macro_gportalETL_Modified_0_0::IPM`, `Codename`, `Rate`, `rawyear`, `$f0`) AS `t68` ON `t37`.`D` = `t68`.`D` ORDER BY `id`) AS `t70` ORDER BY `Product`) AS `t72` WHERE `Year` > `SecondPreviousYear`) AS `t73` WHERE `Product` <> '' AND UPPER(CASE WHEN CASE WHEN PIG_SIZE(`Language`) > 15 THEN SUBSTRING(`Language`, 0, 15) ELSE `Language` END = 'NORWEGIAN (BOKM' THEN 'NORWEGIAN (BOKMAL)' ELSE `Language` END) <> '') AS `t75` INNER JOIN (SELECT CAST(`Qtr` || `Year` AS STRING) AS `DateYear`, `Qtr`, `Year` FROM (SELECT * FROM macro_ipminitalETL_QTRipm_0 ORDER BY `Year`) AS `t76`) AS `t77` ON `t75`.`DateYear` = `t77`.`DateYear`) AS `t78` INNER JOIN (SELECT CAST(UPPER(`Software`) AS STRING) AS `Software`, CAST(UPPER(`Finance`) AS STRING) AS `Finance`, CAST(UPPER(`GlassProductName`) AS STRING) AS `GlassProductName` FROM macro_ipminitalETL_macro_loadProductMapping_Names1_0_0 ORDER BY `GlassProductName`) AS `t80` ON `t78`.`Product` = `t80`.`Finance`) AS `t81` INNER JOIN (SELECT UPPER(TRIM(`Lang`)) AS `Lang`, TRIM(`code`) AS `code`, TRIM(`tier`) AS `tier` FROM macro_ipminitalETL_macro_getLocaleCodes_A1_0_0 ORDER BY `Lang`) AS `t83` ON `t81`.`Language` = `t83`.`Lang` ORDER BY `t81`.`Product`) AS `t85`);