load all combinations

[jordan231]

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 ?

[Peter_Cammaert]

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

[Peter_Cammaert]

No need for FOR loops. They're too slow anyway.

Peter

[giakoum · Accepted solution]

tried to make it as simple as possible. Just add more months.

[Sokkorn]

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

[jordan231]

Guys You are amazing ! Thanks it was what i was looking for ! Thanks a lot !!

Cheers

Jack