Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
...
Hi,
All this combination do you want to be in one Column?
Yes, because in another table we can have different combination of field
USA
USA OR New York
New York AND USD
Sounds like a complex for loop.
For each row in table ...
concatenate with each column...
hi Vitalii,
this could be useful to you.
let me know
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