Discussion Board for collaboration on QlikView Scripting.
I have an issue that am struggling to find a solution.
I have two huge qvd files(3 Gb each) from two tables orders, payments
sample date with just the coulms of intereset is below
pay_method, orderdate, visitnum
A, 1-Jan-13, 123
A, 1-Jan-13, 234
B, 1-Jan-13, 345
G, 1-Jan-13, 456
G, 1-Jan-13, 567
I want to join these two tables based on visitnum. In the output, I need paydate by default for any mapping records. If there is no mapping I need to get the orderdate
The output I need should look like below
pay_method, date, visitnum
B, 2-Jan-13, 345
G, 5-Jan-13, 456
I feel using a case statement in an outer join can give me the required output, but these are huge tables with millons of rows, so I do not want to join the tables in SQL.
Can anyone suggest a way to do this is qlikview while reloading??
Any help is greatly appreciated.
Go to Solution.
The following script gives the result:
Left Join (orders) LOAD visitnum, paydate Resident payments;
DROP Table payments;
NoConcatenate LOAD pay_method, visitnum, If(IsNull(paydate),orderdate,paydate) as date Resident orders;
DROP Table orders;
the two tables(qvd) are very huge around 3Gb each. Using a Resident might cause performance problems.
Can we get away with Resident?
Can you please let me know if using the resident tables causes performance impact as that data is originally loaded from huge qvd files?
If so, do we have to write the output to a seperate qvd and again load the qvd?
what is the beast approcah?
As I am new to qlikview any suggestions would help.
Thanks in advance
Link the two tables with forming key i.e
visitnum &'-'& floor(orderdate) as Key,
orderdate as order_Date,
visitnum &'-'& floor(paydate) as Key,
paydate as pay_date
and then in front end you can use If(IsNull(pay_date),order_Date,pay_date) to get one date