topic Re: Create column based on multiple columns lookup in QlikView

Create column based on multiple columns lookup

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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:

RowID	Type1	Type2	Type4
1	A
2		A
3			A

The field I want created will look like this:

RowID	Type
1	Type 1
2	Type 2
3	Type 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

Re: Create column based on multiple columns lookup

maxgro — Mon, 10 Oct 2016 11:45:11 GMT

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

Re: Create column based on multiple columns lookup

sunny_talwar — Mon, 10 Oct 2016 11:51:10 GMT

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;