Join tables from an excel file and from SQL Server:

[#1]

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?

[Anonymous]

Miguel

Could you share your load script ?

Best Regards,     Bill

[#3]

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]);

[#4]

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);

[Anonymous]

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

[#6]

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)?

[Anonymous]

Miguel

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

[#8]

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

[#9]

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);

[#10 · Accepted solution]

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);