Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vchuprina
New Contributor II

How create table with all possible combination of fields?

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

CountryCityCurrency
USANew YorkUSD
ItalyRomeEuro
EnglandLondonGBP

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

...

5 Replies
mdmukramali
Valued Contributor II

Re: How create table with all possible combination of fields?

Hi,

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

vchuprina
New Contributor II

Re: How create table with all possible combination of fields?

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

USA

USA OR New York

New York AND USD

jpenuliar
Valued Contributor III

Re: How create table with all possible combination of fields?

Sounds like a complex for loop.

For each row in table ...

concatenate  with each column...

leledifrapwc
New Contributor II

Re: How create table with all possible combination of fields?

hi Vitalii,

this could be useful to you.

let me know

Re: How create table with all possible combination of fields?

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

Community Browser