topic Using Concatenate with left join in QlikView

Using Concatenate with left join

Wed, 28 Sep 2011 18:16:14 GMT

I have a situation where I am combining data from 2 data sources. I currently have 6 tables, 3 from each source. I am using Concatenate to combine the 6 tables into 3 usable tables with combined fields.

I have 2 Customer tables, 2 Order tables, and 2 order detail tables.

My problem is in the second data source, I only want to see Closed Orders. This flag is set on the Order table. Normally I would just just a left keep for the order to the order detail table to preserve this as it goes down. How ever I am unable to do this now since I am using Concatenate before the second order detail table.

It basicaly looks like this:

Main source log in;
Customer:
Load 
cust,
custid;
Select 
cust,
custid
From ...;
Order:
Load
custid,
ordernum,
orderamt;
Select
custid,
ordernum,
orderamt
From ...;
Orderdtl:
Load
 ordernum,
orderline,
lineamt;
 Select
ordernum,
orderline,
lineamt
From ...;
Second Source login;
Oldcustomer:
Concatenate(Customer)
Load 
cust,
custid;
Select
cust,
custid
From ...;
OldOrder:
Concatenate(Order)
Load 
custid,
ordernum,
orderamt;
Select
custid,
ordernum,
orderamt,
Status
From ... Where Status='Closed';
OldOrderdtl:
Concatenate(Orderdtl)
Load
ordernum,
orderline,
lineamt;
Select
ordernum,
orderline,
lineamt
From ...;

How can I do this where it only pulls lines from OldOrderdtl were the total order is closed?

Attached picture. Lines on left are the concatenate tables, the line on the right is the join I need. Join needs to be done (data reduced) BEFORE the concatenate happens. Is this possible?

Using Concatenate with left join

Thu, 29 Sep 2011 12:37:10 GMT

Any one know how I can accomplish this? Just moved this to Development community due to no response in New to Qlikview

Using Concatenate with left join

Michiel_QV_Fan — Thu, 29 Sep 2011 12:48:28 GMT

Try this:

Load the table with the data reduction, name for instance: xxx_temp

Load this table again but Resident xxx_tem.

Drop xxx_temp

I hope this helps.

Re: Using Concatenate with left join

Thu, 29 Sep 2011 12:53:57 GMT

Would the script be something like this? I have not worked with temp tables or resident loads so I am not sure of the correct syntax.

OldOrder:
Concatenate(Order)
Load 
custid,
ordernum,
orderamt;
Select
custid,
ordernum,
orderamt,
Status
From ... Where Status='Closed';

OldOrderdtltemp:
left keep(OldOrder)
Load
ordernum,
orderline,
lineamt;
Select
ordernum,
orderline,
lineamt
From ...;
Residentoldline:
Concatenate (Orderline)
Load
ordernum,
orderline,
lineamt;
Select
ordernum,
orderline,
lineamt
From Resident OldOrderdtltemp;
Drop OldOrderdtltemp;

Using Concatenate with left join

Thu, 29 Sep 2011 13:08:24 GMT

You can remove all your concatenations. Field names are the same in the tables and will auto concat anyway. If you don't want it to concatenate you either can use no concatenate load or use a dummy-field.

Easiest way to do what I think I want to accomplish is to switch load order and do something like:

OldOrder:

xxxx

From ... Where Status='Closed';

Oldcustomer:

left keep xxxx

OldOrderdtl:

left keep xxx

Order:

xxxx

Customer:

xxxx

Orderdtl:

xxxx

Using Concatenate with left join

Thu, 29 Sep 2011 13:56:38 GMT

I got it to work by using left keep temp tables for the OldOrder and OldOrderLine tables, then concatenating using the resident tables. Thank you for your help!