Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

Multiple If

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.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

maybe one solution to this question could be:

QlikCommunity_Thread_265692_Pic1.JPG

QlikCommunity_Thread_265692_Pic2.JPG

QlikCommunity_Thread_265692_Pic3.JPG

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

View solution in original post

16 Replies
sunny_talwar

For 1st requirement, may be this

If(Field1='ABC', '123, 234', Field1) as NewField.

sunny_talwar

For second req, what happens to 4, 5, 6, 7?

Anil_Babu_Samineni

How user want to analysis over here, That may helps us to better understand the intend

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
antoniotiman
Master III
Master III

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 

Regards,

Antonio

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Each field value can consist of one text value and one numerical value (dual) or exactly one text value or one numerical value or even nothing (null). But you cannot store two separate text values or two separate numbers in a single Field value.

Strings can be concatenated, as Sunny already demonstrated. Or also using the Concat() function or the & string concatenation operator.

For the second requirement, just create a correct internal table linking 1, 2 and 3 to ABC but not 8. IMHO it serves no use to try to use bad / incorrect data to get the correct ouput...

MK9885
Master II
Master II
Author

Sorry I may have explained wrong.

I'm actually looking for

Ex:

Field1

ABC

CDE

if(Field1='ABC',123, if(Field1='CDE',123

and I also need

if(Field1='ABC',234, if(Field1='CDE',234

So single value having multiple values

ABC, CDE should be both 123 and also 234

sunny_talwar

Nope, I still have no idea what you mean....

MK9885
Master II
Master II
Author

Field1                         NewField1

abc

Value1
bcdValue1
cdeValue1
qweValue3
rteValue3
qwsValue3
abcValue2
bcdValue2
cdeValue2

I'm looking to create new values out of Field1

so I want abc, bcd and cde as Value1 and also abc,bcd and cde as Value 2

sunny_talwar

Then may be concatenate

LOAD blah blah,

     'value1' as NewField

FROM

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


Concatenate

LOAD blah blah,

     'value2' as NewField

FROM

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