topic Re: Join in QlikView

Join

Thu, 20 Mar 2014 13:11:50 GMT

Hi All,

I have data in two tables that I would like to incorporate into one table.

The first table is a customer order table at individual order level (customers can have multiple orders)with Customer ID and Quantity ordered columns ( as well as a lot of other columns). The second table is a Customer despatches table at individual order despatch level. This has customer ID and and quantity despatched columns ( plus other columns).

I now need to create a new table at customer ID level which will have the columns Customer ID , Quantity Ordered, Quantity Despatched.

How can I go about achieving this.

All help appreciated

Herbert

Re: Join

ankit777 — Thu, 20 Mar 2014 13:36:17 GMT

Is there no column in any of the two table like order number which marks a particular order uniquely?

Re: Join

Gysbert_Wassenaar — Thu, 20 Mar 2014 13:51:31 GMT

Usually it's enough to create a straight table with Customer ID as dimension and two expressions: sum([Quantity ordered]) and sum([Quantity Dispatched]). If you want to calculate it in the script you can do something like this:

SummaryTable:

Load [Customer ID], sum([Quantity ordered]) as [Quantity Ordered Customer Total]

Resident Orders

group by [Customer ID];

join (SummaryTable)

Load [Customer ID], sum([Quantity Dispatched]) as [Quantity Dispatched Customer Total]

Resident Dispatches

group by [Customer ID];

Re: Join

Thu, 20 Mar 2014 14:45:50 GMT

Thanks Gysbert,

I am getting a syntax, which says "missing/misplaced FROM: SummaryTable:..." I have converted your code to:

SummaryTable:

LOAD TIMB,

Sum([Bales Loaded]),as [Total Loaded]

Resident [Bale Loop]

group by TIMB;

Thanks Herbert

Re: Join

Gysbert_Wassenaar — Thu, 20 Mar 2014 14:51:36 GMT

You have a comma just before the as that shouldn't be there: Sum([Bales Loaded]),as [Total Loaded]

Re: Join

Thu, 20 Mar 2014 15:30:43 GMT

Fantastic Gysbert,

The join worked perfectly. I have added a bit more to the code to say Total Loaded - Total Delivered.

For some strange reason, the calculation is working fine where there is a value in both columns Total Loaded and Total Delivered. Where there is a value in only one of the columns, it is not performing the calculation. The code now looks like this:

SummaryTable1:

LOAD TIMB,

Sum([Bales Loaded])as [Total Loaded]

Resident [BALE LOOP]

group by TIMB;

Join(SummaryTable1)

LOAD TIMB,

Sum([Bales Delivered]) as [Total Deliveries]

Resident BOOKINGS

Group by TIMB;

[SUMMARYTABLE]:

NoConcatenate LOAD

TIMB,

if([Total Loaded]='',0,[Total Loaded]) as [Total Loaded],

if([Total Deliveries]='',0,[Total Deliveries]) as [Total Deliveries],

[Total Loaded]-[Total Deliveries]

Resident [SummaryTable1]

Order by TIMB;

DROP Table [SummaryTable1];

Please help.

Thanks

Re: Join

Gysbert_Wassenaar — Thu, 20 Mar 2014 15:37:07 GMT

I think in this case what you can use is the rangesum function, which can deal with nulls.

[SUMMARYTABLE]:

NoConcatenate LOAD

TIMB,

[Total Loaded],

[Total Deliveries],

rangesum([Total Loaded],-[Total Deliveries]) as Result

Resident [SummaryTable1]

Order by TIMB;

If you want to test for null values (or blanks) use if(len(trim(SomeField))=0, ...

Re: Join

Thu, 20 Mar 2014 15:45:33 GMT

Gysbert,

You have a very good grasp of QlikView, many thanks for your help. As we would say in this part of the world, You do not deserve to walk amongst mere men.

How long have you been using the product and do you have any tips for someone wanting to learn a whole lot more.

Thank you

Herbert