Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create column based on multiple columns lookup

I'm looking to create a column called 'Type' if the letter A exists in any of the Types1,2,3,4,5. If it does, then the newly created column 'Type' will get given the value of the corresponding column (e.g. Type 1).

The data at the moment:

RowIDType1Type2Type3Type4
1A
2A
3A


The field I want created will look like this:

RowIDType
1Type 1
2Type 2
3Type 4


What I have tried:

If(Match(Type1, 'A', 'Type 1'), if(WildMatch(Type2 ,'A', 'Type 2'), if(WildMatch(Type3, 'A', 'Type 3'), if(WildMatch(Type4, 'A', 'Type 4'),if(WildMatch(Type5, 'A', 'Type 5),if(WildMatch(Type6, 'A', 'Type 6',''))))))) as Type

Which returns the error:

Error in expression:

If takes 2-3 parameters

2 Replies
maxgro
MVP
MVP

SCRIPT

LOAD RowID,

    Type1,

    Type2,

    Type3,

    Type4,

    if(len(trim(Type1))=0, '-', Type1) & if(len(trim(Type2))=0, '-', Type2) & if(len(trim(Type3))=0, '-', Type3) & if(len(trim(Type4))=0, '-', Type4)  as Field

FROM

[https://community.qlik.com/thread/235769]

(html, codepage is 1252, embedded labels, table is @1);



CHART

Expression for Type

'Type' & Index(Field, 'A')

1.png

sunny_talwar

Another option would be to use CrossTable:

Table:

CrossTable(Type, Value)

LOAD RowID,

    Type1,

    Type2,

    Type3,

    Type4

FROM

[https://community.qlik.com/thread/235769]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

NoConcatenate

LOAD RowID,

  Type,

  Value

Resident Table

Where Len(Trim(Value)) > 0;

DROP Table Table;


Capture.PNG