How create table with all possible combination of fields?

[vchuprina]

Hi all,

How can I dynamically generate field with all possible combination of fields.

How can I do this in qlikview

For example:

In table below I have three field

Country	City	Currency
USA	New York	USD
Italy	Rome	Euro
England	London	GBP

Combination for one row:

USA

New York

USD

USA & New York

New York & USA

USA & USD

USD & USA

New York & USD

USD & New York

USA & New York & USD

New York & USD & USA

USD & New York & USA

...

[mdmukramali]

Hi,

All this combination do you want to be in one Column?

[vchuprina]

Yes, because in another table we can have different combination of field

USA

USA OR New York

New York AND USD

[jpenuliar]

Sounds like a complex for loop.

For each row in table ...

concatenate  with each column...

[Anonymous]

hi Vitalii,

this could be useful to you.

let me know

[Peter_Cammaert]

Maybe not. There is a way to do this in a single load but with a lot of expressions. Imagine that the source table containing columns Country, City and Currency is called er... SourceTable !

Combinations:

LOAD RecNo() AS SourceRow,

     IterNo() AS Iteration,

     Pick(IterNo(),

          Country,

          City,

          Currency,

          Country & ' & ' & City,

          City & ' & ' & Country,

          Country & ' & ' & Currency,

          Currency& ' & ' & Country,

          City & ' & ' & Currency,

          Currency& ' & ' & City,

          Country & ' & ' & City & ' & ' & Currency,

          Country & ' & ' & Currency& ' & ' & City,

          City & ' & ' & Country & ' & ' & Currency,

          City & ' & ' & Currency& ' & ' & Country,

          Currency& ' & ' & Country & ' & ' & City,

          Currency& ' & ' & City & ' & ' & Country

        ) AS CountryCityCurrencyCombination

RESIDENT SourceTable

WHILE IterNo() <= 15;

Best,

Peter