topic Re: Left Join in QlikView

Left Join

ziadm — Sun, 07 Jun 2015 02:46:57 GMT

I have the following script

FACT:

LOAD ReportedCustomerName,

PostalCode,

PartNumber,

Quantity,

Resale,

ExtendedResale,

CommissionPaid,

CommissionPerct,

Channel,

ChannelIdentifier,

CM,

ShippedDate,

PaidDate,

Principal

FROM

(ooxml, embedded labels, table is Data);

Left Join

LOAD

ShippedDate,

'1' as Flag

FROM

(ooxml, embedded labels);

There are 6 files is Sales*.xlsx and 5 files is COMMT.*.xlsx

The load of sales file goes ok loadind sales files only when it joins on the 3 rd COMM file the script loading hangs any idea

Thanks

Re: Left Join

ToniKautto — Sun, 07 Jun 2015 03:26:34 GMT

The wildcard file name does not mean that all files are load in one load, it leads to multiple loads being executed. You should be able to confirm this behavior by looking in the document log.

The first load statement where FACT is created will likely not cause you any practical issues. The Left Join load will however multiple the length of you table to a much higher number than you are expecting. To get around this you need to do the wildcard load into a temporary table, and then join the temporary table to the FACT table.

My guess is that the Shipped Dates should be distinct in the temporary table. This could be accomplishes by only loading dates that do not already exist in the temporary table.

TmpFlag:
LOAD
     ShippedDate,
     '1' as Flag
FROM

(ooxml, embedded labels)
Where Not Exists(ShippedDate);

Left Join ('FACT')
NoConcatenate LOAD *
Resident TmpFlag;

DROP Table TmpFlag;

Re: Left Join

ziadm — Sun, 07 Jun 2015 05:15:45 GMT

Thanks Toni

Re: Left Join

ToniKautto — Mon, 08 Jun 2015 03:30:41 GMT

You're welcome!

If that resolved your issue I would suggest that you mark it as correct instead of helpful, or please let me know if it did resolve your issue.