Qlik Community

Ask a Question

New to QlikView

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

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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
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

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