Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dears, i'm quite new in QlikView and i'm facing a problem with some kind of load.
I have two tables one consist information about cost centers and second one accounts.
From this source i need to load unique ID's for all possible combination. MinimumDate is January 2013 and Maximum Date is December 2013 (last month dates)
CostCenterTable:
10
20
AccountTable:
A
B
So the result i would like to have is 24 records
10A20130131
10A20130228
10A20130331
...
20B20141131
20B20141231
Probably i should use some loop but seems that i stuck with that. Anyone able to help me ?
tried to make it as simple as possible. Just add more months.
Perform a cartesian product between the first two tables, i.e. JOIN them without a common field. Then construct a list with MonthEnd() dates and do the same JOIN to the first merged table. Finally, for every row in the table, concatenate three fields à la CostCenter & Account & Date as your required key field.
Peter
No need for FOR loops. They're too slow anyway.
Peter
tried to make it as simple as possible. Just add more months.
Hi,
Here is the load script
For i=1 to 12
[Period]:
LOAD
MonthEnd(MakeDate(2013,$(i),1)) As Period
AutoGenerate(1);
NEXT i;
AccountTable:
Join (Period)
LOAD * Inline [
FieldAcc
A
B];
CostCenterTable:
Join (Period)
LOAD * Inline [
FieldCC
10
20];
[Result]:
LOAD
RowNo() As ID,
FieldCC & FieldAcc & Date(Period,'YYYYMMDD') As NewField
Resident [Period] Order By Period;
DROP Table [Period];
Regards,
Sokkorn
Guys You are amazing ! Thanks it was what i was looking for ! Thanks a lot !!
Cheers
Jack