topic Re: Need help with joining two tables in QlikView

Need help with joining two tables

Wed, 13 Jan 2016 12:45:09 GMT

Hey everybody!

I need to join these two tables:

first table is a table of ids, types of expense and ammount for every pay date...

Date	ID	Expense Type	Expense Ammount
01/02/2015	1	Car	100
01/03/2015	1	Parking	200

second table is a range of all the dates from min to max in the first table:

Date
01/02/2015
02/02/2015
03/02/2015

I want to join them so that every ID+Type pair would be paired with every date in the second table with only ammount in the new dates left blank (and later i would propagate them...)

After the join I would like to be able to order the new table like this:

Date	ID	Expense Type	Expense Ammount
01/01/2015	1	Car	100
02/01/2015	1	Car	-
03/01/2015	1	Car	-
01/01/2015	1	Parking	200
02/01/2015	1	Parking	-
03/01/2015	1	Parking	-

So that in the script i can create a running total for each category...

How should i go about this?

Re: Need help with joining two tables

swuehl — Wed, 13 Jan 2016 14:03:43 GMT

Maybe like

T1:
LOAD Date,
    ID,
    [Expense Type],
    [Expense Ammount]
FROM
[https://community.qlik.com/thread/199407]
(html, codepage is 1252, embedded labels, table is @1);


T2:
LOAD Date
FROM
[https://community.qlik.com/thread/199407]
(html, codepage is 1252, embedded labels, table is @2);




JOIN (T2) LOAD DISTINCT ID, [Expense Type] RESIDENT T1;


Left JOIN (T2) LOAD * RESIDENT T1;


RESULT:
NOCONCATENATE LOAD Date, ID,  [Expense Type], [Expense Ammount]
RESIDENT T2
ORDER BY ID, [Expense Type], Date;


DROP TABLES T1, T2;

Note that this won't fully replicate your third table, since your second table does not show the full range of dates of the first table. I assume this is a typo / error in your post.

Re: Need help with joining two tables

Wed, 13 Jan 2016 15:20:14 GMT

First thing first, thank you so much! I was really starting to lose hope...

Any chance you can explain what you did there? It's working perfect but im still not sure i understand...

Re: Need help with joining two tables

swuehl — Wed, 13 Jan 2016 17:31:55 GMT

Well, I just followed your recipe:

"I want to join them so that every ID+Type pair would be paired with every date in the second table with only ammount in the new dates left blank (and later i would propagate them...)"

JOIN (T2) LOAD DISTINCT ID, [Expense Type] RESIDENT T1;
Left JOIN (T2) LOAD * RESIDENT T1;

After the join I would like to be able to order the new table like this"

RESULT:
NOCONCATENATE LOAD Date, ID, [Expense Type], [Expense Ammount]
RESIDENT T2
ORDER BY ID, [Expense Type], Date;