Creating a new dimension based on two other dimensions

[EdgarB]

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

[Or]

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.

[EdgarB]

Thank you very much, it works. Have a nice day  🙂

[EdgarB]

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 ? 

[Or]

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.

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

[EdgarB]

There is one table, 21 columns and around 500,000 lines.

[Or]

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.

[Digvijay_Singh]

Can you share at least 10-15 rows of sample data? want to see how other columns look like..

[MayilVahanan]

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:

[EdgarB]

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.