topic Re: Join tables from an excel file and from SQL Server: in QlikView

Join tables from an excel file and from SQL Server:

Fri, 22 Nov 2013 11:04:06 GMT

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?

Re: Join tables from an excel file and from SQL Server:

Anonymous — Fri, 22 Nov 2013 11:18:35 GMT

Miguel

Could you share your load script ?

Best Regards, Bill

Re: Join tables from an excel file and from SQL Server:

Fri, 22 Nov 2013 11:51:06 GMT

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

Re: Join tables from an excel file and from SQL Server:

Fri, 22 Nov 2013 13:55:24 GMT

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

Re: Re: Join tables from an excel file and from SQL Server:

Anonymous — Fri, 22 Nov 2013 20:34:20 GMT

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

Re: Join tables from an excel file and from SQL Server:

Mon, 25 Nov 2013 11:03:34 GMT

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

Re: Re: Join tables from an excel file and from SQL Server:

Anonymous — Mon, 25 Nov 2013 11:23:56 GMT

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

Re: Re: Join tables from an excel file and from SQL Server:

Mon, 25 Nov 2013 11:27:09 GMT

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

Re: Join tables from an excel file and from SQL Server:

Mon, 25 Nov 2013 15:45:54 GMT

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

Re: Join tables from an excel file and from SQL Server:

Mon, 25 Nov 2013 16:31:41 GMT

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