Need help with joining two tables

[#1]

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?

[swuehl · Accepted solution]

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.

[#3]

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...

[swuehl]

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...)"

1. JOIN (T2) LOAD DISTINCT ID, [Expense Type] RESIDENT T1; 
2.  
3.  
4. Left JOIN (T2) LOAD * RESIDENT T1; 
5.  

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

1. RESULT: 
2. NOCONCATENATE LOAD Date, ID,  [Expense Type], [Expense Ammount] 
3. RESIDENT T2 
4. ORDER BY ID, [Expense Type], Date;