Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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

Highlighted

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