Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Tried to do this the traditional way (just load the 2 tables and fields with the same name will be automatically joined) but the fields I load in second place from the excel file are not available after loading.
Maybe I should save the 2 tables first to a qvd and then load the qvd's again so I can join them?
My previous answer just had a small bug: I was using the + sign instead of the & sign to concatenate strings.
Here's the final solution:
TEMP_BILLING_FREQUENCY:
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code" as Client_Code,
"Contract_Name",
"StartDate",
"EndDate",
"Region",
"BillingFrequency",
"Consumption_Type",
"Billing_Type",
"RU_Name",
"BillingFrequency_Description",
"BillingFrequency" as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
BILLING_FREQUENCY_MAPPING:
Mapping LOAD
BillingFrequency & '_' & BillingFrequency_Description, BILLING_FREQUENCY_LEVEL1
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
BILLING_FREQUENCY:
load
0.3 as [WEIGHTED RECORD_CONTRACT COUNT],
ApplyMap('BILLING_FREQUENCY_MAPPING' , TEMP_BILLING_FREQUENCY.BillingFrequency & '_' & TEMP_BILLING_FREQUENCY.BillingFrequency_Description, 'UNKNOWN' ) as BILLING_FREQUENCY_LEVEL1,
TEMP_BILLING_FREQUENCY.Client_Code as Client_Code,
TEMP_BILLING_FREQUENCY.Contract_Name as Contract_Name,
TEMP_BILLING_FREQUENCY.StartDate as StartDate,
TEMP_BILLING_FREQUENCY.EndDate as EndDate,
TEMP_BILLING_FREQUENCY.Region as Region,
TEMP_BILLING_FREQUENCY.BillingFrequency as BillingFrequency,
TEMP_BILLING_FREQUENCY.Consumption_Type as Consumption_Type,
TEMP_BILLING_FREQUENCY.Billing_Type as Billing_Type,
TEMP_BILLING_FREQUENCY.RU_Name as RU_Name,
TEMP_BILLING_FREQUENCY.BillingFrequency_Description as BillingFrequency_Description,
TEMP_BILLING_FREQUENCY.Source as Source
resident TEMP_BILLING_FREQUENCY;
STORE BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\BILLING_FREQUENCY.qvd](qvd);
Miguel
Could you share your load script ?
Best Regards, Bill
First Attempt:
TEMP_BILLING_FREQUENCY:
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code",
"Contract_Name",
"StartDate",
"EndDate",
"Region",
"BillingFrequency",
"Consumption_Type",
//"BILLING_FREQUENCY_LEVEL1",
"Billing_Type",
"RU_Name",
"BillingFrequency_Description",
"BillingFrequency" as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
BILLING_FREQUENCY_MAPPING:
LOAD BILLING_FREQUENCY_LEVEL1,
BillingFrequency,
BillingFrequency_Description
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
Load *
resident TEMP_BILLING_FREQUENCY;
inner join(TEMP_BILLING_FREQUENCY)
BILLING_FREQUENCY:
load*
resident BILLING_FREQUENCY_MAPPING;
DROP TABLE BILLING_FREQUENCY_MAPPING;
DROP TABLE TEMP_BILLING_FREQUENCY;
STORE BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\BILLING_FREQUENCY.qvd](qvd);
Second attempt:
TEMP_BILLING_FREQUENCY:
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code",
"Contract_Name",
"StartDate",
"EndDate",
"Region",
BillingFrequency,
"Consumption_Type",
//"BILLING_FREQUENCY_LEVEL1",
"Billing_Type",
"RU_Name",
BillingFrequency_Description,
"BillingFrequency" as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
STORE TEMP_BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\TEMP_BILLING_FREQUENCY.qvd](qvd);
//DROP TABLE TEMP_BILLING_FREQUENCY;
Inner join (TEMP_BILLING_FREQUENCY)
LOAD BILLING_FREQUENCY_LEVEL1,
BillingFrequency,
BillingFrequency_Description
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
My last attempt also didn't work, saving on QVD files first:
QUALIFY *;
TEMP_BILLING_FREQUENCY:
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code",
"Contract_Name",
"StartDate",
"EndDate",
"Region",
BillingFrequency,
"Consumption_Type",
//"BILLING_FREQUENCY_LEVEL1",
"Billing_Type",
"RU_Name",
BillingFrequency_Description,
'BILLING_FREQUENCY' as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
STORE TEMP_BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\TEMP_BILLING_FREQUENCY.qvd](qvd);
DROP TABLE TEMP_BILLING_FREQUENCY;
TEMP_BILLING_FREQUENCY_LEVEL1:
LOAD BILLING_FREQUENCY_LEVEL1,
BillingFrequency,
BillingFrequency_Description
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
STORE TEMP_BILLING_FREQUENCY_LEVEL1 into [..\..\..\QVD\WW\CSI_Dashboard\TEMP_BILLING_FREQUENCY_LEVEL1.qvd](qvd);
DROP TABLE TEMP_BILLING_FREQUENCY_LEVEL1;
BILLING_FREQUENCY:
LOAD TEMP_BILLING_FREQUENCY.Client_Code as Client_Code,
TEMP_BILLING_FREQUENCY.Contract_Name as Contract_Name,
TEMP_BILLING_FREQUENCY.StartDate as StartDate,
TEMP_BILLING_FREQUENCY.EndDate as EndDate,
TEMP_BILLING_FREQUENCY.Region as Region,
TEMP_BILLING_FREQUENCY.BillingFrequency as BillingFrequency,
TEMP_BILLING_FREQUENCY.Consumption_Type as Consumption_Type,
TEMP_BILLING_FREQUENCY.Billing_Type as Billing_Type,
TEMP_BILLING_FREQUENCY.RU_Name as RU_Name,
TEMP_BILLING_FREQUENCY.BillingFrequency_Description as BillingFrequency_Description,
TEMP_BILLING_FREQUENCY.Source as Source
FROM
[..\..\..\QVD\WW\CSI_Dashboard\TEMP_BILLING_FREQUENCY.qvd]
(qvd);
inner join(BILLING_FREQUENCY)
LOAD TEMP_BILLING_FREQUENCY_LEVEL1.BILLING_FREQUENCY_LEVEL1 as BILLING_FREQUENCY_LEVEL1,
TEMP_BILLING_FREQUENCY_LEVEL1.BillingFrequency as BillingFrequency,
TEMP_BILLING_FREQUENCY_LEVEL1.BillingFrequency_Description as BillingFrequency_Description
FROM
(qvd);
Miguel
I am guessing what you are trying but it looks like you wish to replace the BILLING_FREQUENCY_LEVEL1 held on your database with the value held in your xlsx.
If that is the case I'd suggest a Mapping Load and an ApplyMap() via a Preceding Load, as in something like this:
Level1Map :
Mapping LOAD
BillingFrequency,
BILLING_FREQUENCY_LEVEL1
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
BILLING_FREQUENCY:
Load
ApplyMap('Level1Map' , [BillingFrequency] ) as BILLING_FREQUENCY_LEVEL1
*
;
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code",
"Contract_Name",
"StartDate",
"EndDate",
"Region",
BillingFrequency,
"Consumption_Type",
//"BILLING_FREQUENCY_LEVEL1",
"Billing_Type",
"RU_Name",
BillingFrequency_Description,
'BILLING_FREQUENCY' as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
I may badly guessed, in which case please clarify.
Best Regards, Bill
Almost.
I need to link BillingFrequency + BillingFrequency_Description with BILLING_FREQUENCY_LEVEL1.
So my old script was:
BILLING_FREQUENCY:
LOAD [WEIGHTED RECORD_CONTRACT COUNT],
Client_Code,
Contract_Name,
StartDate,
EndDate,
Region,
BillingFrequency,
Consumption_Type,
BILLING_FREQUENCY_LEVEL1,
Billing_Type,
RU_Name,
BillingFrequency_Description,
'BILLING_FREQUENCY' as Source
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY.xlsx]
(ooxml, embedded labels, table is [Raw Data]);
STORE BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\BILLING_FREQUENCY.qvd](qvd);
And the new script is:
BILLING_FREQUENCY:
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code",
"Contract_Name",
"StartDate",
"EndDate",
"Region",
"BillingFrequency",
"Consumption_Type",
//"BILLING_FREQUENCY_LEVEL1",
"Billing_Type",
"RU_Name",
"BillingFrequency_Description",
"BillingFrequency" as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
LOAD BILLING_FREQUENCY_LEVEL1,
BillingFrequency,
BillingFrequency_Description
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
STORE BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\BILLING_FREQUENCY.qvd](qvd);
Does your ApplyMap maps 2 columns into 1 (like BillingFrequency + BillingFrequency_Description into BILLING_FREQUENCY_LEVEL1)?
Sorry if I am being stupid, could you clarify the end result you want when you say:
Does your ApplyMap maps 2 columns into 1 (likeBillingFrequency + BillingFrequency_Description intoBILLING_FREQUENCY_LEVEL1)?
Are you after :
A concatenated key of BillingFrequency + BillingFrequency_Description for the link ?
Or ending up with 2 separate fields for BillingFrequency & BillingFrequency_Description ?
Best Regards, Bill
I want both.
So I'm after:
A concatenated key of BillingFrequency +BillingFrequency_Description for the link
And ending up with 3 separate fields in the final table for BillingFrequency, BillingFrequency_Description, BILLING_FREQUENCY_LEVEL1.
Thanks,
Miguel
This is my last attempt so far, but unfortunately it shows ANNUALLY in all rows for the column BILLING_FREQUENCY_LEVEL1:
TEMP_BILLING_FREQUENCY:
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code" as Client_Code,
"Contract_Name",
"StartDate",
"EndDate",
"Region",
"BillingFrequency",
"Consumption_Type",
//"BILLING_FREQUENCY_LEVEL1",
"Billing_Type",
"RU_Name",
"BillingFrequency_Description",
"BillingFrequency" as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
BILLING_FREQUENCY_MAPPING:
Mapping LOAD
BillingFrequency + '_' + BillingFrequency_Description, BILLING_FREQUENCY_LEVEL1
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
BILLING_FREQUENCY:
load
0.3 as [WEIGHTED RECORD_CONTRACT COUNT],
ApplyMap('BILLING_FREQUENCY_MAPPING' , TEMP_BILLING_FREQUENCY.BillingFrequency + '_' + TEMP_BILLING_FREQUENCY.BillingFrequency_Description, ' ' ) as BILLING_FREQUENCY_LEVEL1,
TEMP_BILLING_FREQUENCY.Client_Code as Client_Code,
TEMP_BILLING_FREQUENCY.Contract_Name as Contract_Name,
TEMP_BILLING_FREQUENCY.StartDate as StartDate,
TEMP_BILLING_FREQUENCY.EndDate as EndDate,
TEMP_BILLING_FREQUENCY.Region as Region,
TEMP_BILLING_FREQUENCY.BillingFrequency as BillingFrequency,
TEMP_BILLING_FREQUENCY.Consumption_Type as Consumption_Type,
TEMP_BILLING_FREQUENCY.Billing_Type as Billing_Type,
TEMP_BILLING_FREQUENCY.RU_Name as RU_Name,
TEMP_BILLING_FREQUENCY.BillingFrequency_Description as BillingFrequency_Description,
TEMP_BILLING_FREQUENCY.Source as Source
resident TEMP_BILLING_FREQUENCY;
STORE BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\BILLING_FREQUENCY.qvd](qvd);
My previous answer just had a small bug: I was using the + sign instead of the & sign to concatenate strings.
Here's the final solution:
TEMP_BILLING_FREQUENCY:
SQL SELECT CSI.dbo."IN_Contracts"."Client_Code" as Client_Code,
"Contract_Name",
"StartDate",
"EndDate",
"Region",
"BillingFrequency",
"Consumption_Type",
"Billing_Type",
"RU_Name",
"BillingFrequency_Description",
"BillingFrequency" as Source
FROM CSI.dbo."IN_ResourceUnits"
INNER JOIN CSI.dbo."IN_Contracts" ON CSI.dbo."IN_Contracts"."Snapshot_Date" = CSI.dbo."IN_ResourceUnits"."Snapshot_Date"
AND CSI.dbo."IN_Contracts"."Client_Code" = CSI.dbo."IN_ResourceUnits"."Client_Code";
BILLING_FREQUENCY_MAPPING:
Mapping LOAD
BillingFrequency & '_' & BillingFrequency_Description, BILLING_FREQUENCY_LEVEL1
FROM
[..\..\..\Data\WW\CSI_Dashboard\CSI Reporting_w3_BILLING FREQUENCY_MAPPING.xlsx]
(ooxml, embedded labels, table is [CSI Data]);
BILLING_FREQUENCY:
load
0.3 as [WEIGHTED RECORD_CONTRACT COUNT],
ApplyMap('BILLING_FREQUENCY_MAPPING' , TEMP_BILLING_FREQUENCY.BillingFrequency & '_' & TEMP_BILLING_FREQUENCY.BillingFrequency_Description, 'UNKNOWN' ) as BILLING_FREQUENCY_LEVEL1,
TEMP_BILLING_FREQUENCY.Client_Code as Client_Code,
TEMP_BILLING_FREQUENCY.Contract_Name as Contract_Name,
TEMP_BILLING_FREQUENCY.StartDate as StartDate,
TEMP_BILLING_FREQUENCY.EndDate as EndDate,
TEMP_BILLING_FREQUENCY.Region as Region,
TEMP_BILLING_FREQUENCY.BillingFrequency as BillingFrequency,
TEMP_BILLING_FREQUENCY.Consumption_Type as Consumption_Type,
TEMP_BILLING_FREQUENCY.Billing_Type as Billing_Type,
TEMP_BILLING_FREQUENCY.RU_Name as RU_Name,
TEMP_BILLING_FREQUENCY.BillingFrequency_Description as BillingFrequency_Description,
TEMP_BILLING_FREQUENCY.Source as Source
resident TEMP_BILLING_FREQUENCY;
STORE BILLING_FREQUENCY into [..\..\..\QVD\WW\CSI_Dashboard\BILLING_FREQUENCY.qvd](qvd);