Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
EdgarB
Contributor II
Contributor II

Creating a new dimension based on two other dimensions

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 : 

Col1Col2
AZ
BY
CZ
DX


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

Labels (1)
  • Other

1 Solution

Accepted Solutions
EdgarB
Contributor II
Contributor II
Author

=pick(match($Field,'Market','Market Type'),[Market],[Market Type])

View solution in original post

12 Replies
Or
Master
Master

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
Contributor II
Contributor II
Author

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

EdgarB
Contributor II
Contributor II
Author

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 ? 

EdgarB_0-1634130477374.png

 

Or
Master
Master

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
Contributor II
Contributor II
Author

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

Or
Master
Master

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:

MayilVahanan_0-1634180683968.png

 

Thanks & Regards,
Mayil Vahanan R
EdgarB
Contributor II
Contributor II
Author

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 :

EdgarB_0-1634218043756.png

 

My goal is to create a new dimension with all distinct values of Market and Market Type without creating any duplicates.