topic Re: Multiple If in QlikView

Multiple If

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

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.

Re: Multiple If

sunny_talwar — Tue, 27 Jun 2017 16:09:50 GMT

For 1st requirement, may be this

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

Re: Multiple If

sunny_talwar — Tue, 27 Jun 2017 16:10:38 GMT

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

Re: Multiple If

Anil_Babu_Samineni — Tue, 27 Jun 2017 16:11:07 GMT

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

Re: Multiple If

antoniotiman — Tue, 27 Jun 2017 16:19:36 GMT

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

Re: Multiple If

Peter_Cammaert — Tue, 27 Jun 2017 16:23:12 GMT

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...

Re: Multiple If

MK9885 — Tue, 27 Jun 2017 16:53:56 GMT

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

Re: Multiple If

sunny_talwar — Tue, 27 Jun 2017 17:00:32 GMT

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

Re: Multiple If

MK9885 — Tue, 27 Jun 2017 17:19:10 GMT

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

Re: Multiple If

sunny_talwar — Tue, 27 Jun 2017 17:23:32 GMT

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

Re: Multiple If

MK9885 — Tue, 27 Jun 2017 17:27:42 GMT

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.

Re: Multiple If

sunny_talwar — Tue, 27 Jun 2017 17:32:08 GMT

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

Re: Multiple If

MK9885 — Tue, 27 Jun 2017 17:51:05 GMT

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

Re: Multiple If

MK9885 — Tue, 27 Jun 2017 17:55:32 GMT

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..

Re: Multiple If

MK9885 — Tue, 27 Jun 2017 17:59:02 GMT

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.

Re: Multiple If

MarcoWedel — Tue, 27 Jun 2017 23:13:33 GMT

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%20Worksheet%20(4).xlsx] (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

Re: Multiple If

MK9885 — Wed, 28 Jun 2017 13:03:13 GMT

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

Thanks.