Multiple If

[MK9885]

Hi,

I've a requirement to show same value as 2 different values....

Ex:

Field1

ABC

CDE

I want to show If(Field1='ABC', 123, if(Field1='ABC','234', Field1) as NewField.

Is this possible to show same value ABC as 2 different values?

ABC should be both 123 and 234 I tried but it only shows 123 but not 234 or it doesn't show either.

Please help.

And also I've another requirement where a field has codes

Ex:

Field1     Field2

1               ABC

2               ABC

3               ABC

8               ABC

4               BCD

5               BCD

6               BCD

7               CDE

I want to show who ever is falling under Field1 1,2,3 as ABC bot not 8

So if Field1= 1,2,3, ABC...

I want to ignore 8 though it is ABC

Grades only for 1,2,3 are to be considered as ABC

Maybe match or Applymap, not sure.

neelamsaroha1575‌

stalwar1‌

Thanks.

[MK9885]

I do not have NewField1 field

I need to create that out of Field1 which should have multiple values for multiple values.

abc should be 123 and abc should also be 234

Sorry for confusion.

[sunny_talwar]

My friend that is exactly what I am doing....

LOAD blah blah,

     123 as NewField

FROM

WHERE Match(Field1, 'ABC', 'CDE');

Concatenate

LOAD blah blah,

     234 as NewField

FROM

WHERE Match(Field1, 'ABC', 'CDE');

Although I am going to ask, why are you hesitant in posting a sample which might speed up the helping process? Have you read this?

How to get answers to your post?

I mean for a person who is new here, I can totally understand not knowing all this. You have been an active participant here and I fail to understand why a person like yourself (who also help other people) won't post a more informative thread. Help us help you by giving more information the first time. No one like guessing here

Best,

Sunny

[MK9885]

I agree but this question doesn't have much of a sample data...

It is just a field with numerous values where I have to get those multiple values into only 2 or 3 values.

You can see the attachment...

It is only single field

And expected output is

BLACK

RED

GREEN

should be Colour1

BLACK

RED

GREEN Should also be Colour2

BLUE

WHITE

PINK

BLACK

should be Color3

and

BLUE

WHITE

PINK

BLACK should also be Color4

[MK9885]

Hi,

I tried your expression and it works as expected

But when I'm using the same in my transformation which has multiple values

Ex:

If(Field2='ABC',If(Match(Field1,'1','2','3'),Field1),Field1) as Field1,

If(Field2='ABC',If(Match(Field1,'1','2','3'),Field2),Field2) as NewField

how do we do same for BCD?

If(Field2='ABC',If(Match(Field1,'1','2','3'),Field1),

If(Field2='BCD',If(Match(Field1,'4','5'),Field1)

Field1) as Field1,

If(Field2='ABC',If(Match(Field1,'1','2','3'),Field2),

If(Field2='BCD',If(Match(Field1,'4','5'),Field2)Field2) as NewField

I tried it this way but doesn't work... no value shows up..

[MK9885]

For 1st question user wants to get multiple values under 2 different values

ABC can be 123 and ABC can be 234

And for 2nd user want to ignore few codes related to a text field

ABC has codes 12345678

User want to take codes which are only 12345 as ABC and rest ignore it.

Thanks.

[MarcoWedel · Accepted solution]

Hi,

maybe one solution to this question could be:

table1:
LOAD RecNo() as ID,
    FIELD
FROM [https://community.qlik.com/servlet/JiveServlet/download/1296059-284913/New%20Microsoft%20Excel%20Wor...] (ooxml, embedded labels, table is Sheet1);

tabTemp:
CrossTable (Colour, IsColour)
LOAD ID,
    Match(FIELD,'BLACK','RED','GREEN') as Colour1,
    Match(FIELD,'BLACK','RED','GREEN') as Colour2,
    Match(FIELD,'BLUE','WHITE','PINK','BLACK') as Colour3,
    Match(FIELD,'BLUE','WHITE','PINK','BLACK') as Colour4
Resident table1;

tabColours:
LOAD ID, Colour
Resident tabTemp
Where IsColour;

DROP Table tabTemp;

hope this helps

regards

Marco

[MK9885]

Thanks Marco, I'll implement this script in my model and hope it works..

Thanks.