topic Re: load all combinations in QlikView

load all combinations

jordan231 — Mon, 10 Feb 2014 10:20:51 GMT

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:

AccountTable:
A

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 ?

Re: load all combinations

Peter_Cammaert — Mon, 10 Feb 2014 10:25:58 GMT

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

Re: load all combinations

Peter_Cammaert — Mon, 10 Feb 2014 10:26:57 GMT

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

Peter

Re: load all combinations

giakoum — Mon, 10 Feb 2014 10:27:51 GMT

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

Re: load all combinations

Sokkorn — Mon, 10 Feb 2014 10:34:06 GMT

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

Re: load all combinations

jordan231 — Mon, 10 Feb 2014 10:37:35 GMT

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

Cheers

Jack