Solved: Re: Multiple If - Qlik Community

MK9885 · ‎2017-06-27

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.

MarcoWedel · ‎2017-06-27

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

View solution in original post

sunny_talwar · ‎2017-06-27

For 1st requirement, may be this

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

sunny_talwar · ‎2017-06-27

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

Anil_Babu_Samineni · ‎2017-06-27

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 · ‎2017-06-27

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 · ‎2017-06-27

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 · ‎2017-06-27

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 · ‎2017-06-27

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

MK9885 · ‎2017-06-27

Field1 NewField1

abc	Value1
bcd	Value1
cde	Value1
qwe	Value3
rte	Value3
qws	Value3
abc	Value2
bcd	Value2
cde	Value2

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 · ‎2017-06-27

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