Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlikers,
I don't know if it is the place to ask this question. I hope somebody knows the answer.
I have two dimensions Col1 and Col2 from the same table :
Col1 | Col2 |
A | Z |
B | Y |
C | Z |
D | X |
And i want to create a new dimension Col3 which looks like this :
Col3 |
A |
B |
C |
D |
Z |
Y |
X |
How can I get this new dimension with set analysis ? And Within the load script ?
The final goal is put all values of Col1 and Col2 in a filter visualisation as if it was one unique column.
Thank you very much.
Ed
=pick(match($Field,'Market','Market Type'),[Market],[Market Type])
TempTable:
Load Key, Col1 as Col3
Resident YourTable;
Concatenate
Load Key, Col2 as Col3
Resident YourTable;
Join(YourTable)
Load * Resident TempTable;
Drop Table TempTable;
Note that this will return all values from both columns, including duplicates. If you want to avoid duplicates you'll have to add a Load DISTINCT on the Col3 table.
Thank you very much, it works. Have a nice day 🙂
Hi,
Actually with this solution I have dupliactes even with the distinct beacause I have other columns in the table.
The picture bellow maybe resumes better what I have and what I want .
Do you have any idea how to do it ?
With an unknown number of columns, it sounds like you might be looking for the Crosstable prefix. It's hard to be sure without knowing the exact structure.
There is one table, 21 columns and around 500,000 lines.
It'll be a little slow, but you could repeat the process 21 times. Assuming you need the dimensions to match up with the existing rows, I don't see a good way to do it quicker other than possibly Crosstable (as mentioned above), though others may have a technique for this. That said, 500k lines times 21 shouldn't take all that long on a resident load, so I'd probably be inclined to just do that.
Can you share at least 10-15 rows of sample data? want to see how other columns look like..
Hi
Might be, try like below
Table1:
LOAD * INLINE [
SON, FATHER, GRANDFATHER
Little John, John, Big John
Little Peter, Peter, PL
Little John,Johnny,PJ
Little Mary, Mary, Big Mary
];
For i=1 to NoOfFields('Table1')
FieldNames:
Load FieldName($(i), 'Table1') as F AutoGenerate 1;
NEXT
CombineFields:
Load chr(39)&Concat(F, chr(39)&','&chr(39))&chr(39) as Fields Resident FieldNames;
Let vFields = Peek('Fields');
DROP Table FieldNames;
Final:
Load * Inline
[
Dummy
];
For each v in $(vFields)
Concatenate(Final)
Load Distinct $(v) as SingleField Resident Table1;
NEXT
DROP Table CombineFields, Table1;
DROP Field Dummy;
O/P:
Hi
Thank you all for your responses. I think the answer i am looking for is more in the set analysis maybe using pick(match.... something.
However here is a short sample of what my table looks like :
My goal is to create a new dimension with all distinct values of Market and Market Type without creating any duplicates.