topic Re: RE:Change if in QlikView

RE:Change if

Chanty4u — Fri, 05 Feb 2016 08:36:18 GMT

Hi all ,

swuehl,M G,Peter Cammaert,jagan mohan rao appala,Marco Wedel

How to change the below if statements to case statements?

Anyone help me on this.

If(MATCH_TYPE='VOD', 'Tier 1',

If(NOT MATCH_TYPE(NETWORK_TYPE,'Broadcast','Cable') AND NOT IsNull(PROGRAM_NAME),

If(Num#(Left(TIMESHIFT_INDICATOR_ID,2)) >= 10 AND Num(Left(TIMESHIFT_INDICATOR_ID,2)) <= 16

AND Count(Distinct USERS_META_ID) <> 0,'Tier 2','Tier 1')

)

) as Field_name

if(MATCH_TYPE = 'VOD','Tier 1',

if(MATCH_TYPE <> 'VOD' AND NETWORK_TYPE IN ('Broadcast', 'Cable') AND PROGRAM_NAME <> null,

if(TIMESHIFT_INDICATOR_ID = 10 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 11 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 12 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 13 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 14 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 15 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 16 AND SAMPLE <> 0,'Tier 2','Tier 1')))))))))

as field_name

Message was edited by: suri qv

Re: RE:Change if

sunny_talwar — Fri, 05 Feb 2016 08:38:12 GMT

Hey Suresh, Are these two different if statements or the same done differently?

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 08:39:56 GMT

two differnt stemnts sunny... tried with two ways....

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 08:41:07 GMT

sry two r same done in diffrnt ways...

Re: RE:Change if

sunny_talwar — Fri, 05 Feb 2016 08:43:20 GMT

When you say change the if statement to case statement, I am getting confused about which one is the one we need to change to case (besides does QlikView even has a case statement? never seen anybody use it )

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 08:45:07 GMT

hmmm.. dat fr in Qliksense. and two stnts to get ouput same value....tryng to do by usng case stmnts sunny...

please help me how to convert that stmnt to case in general.

Re: RE:Change if

sunny_talwar — Fri, 05 Feb 2016 08:47:26 GMT

I guess, I am going to leave this for other experts, as I have no expertise in using Case Statement. I look at this as an opportunity to learn something new. All the best

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 08:49:09 GMT

sunny.........thnx for info sunny...u r rock always...thaliva.

Re: RE:Change if

Fri, 05 Feb 2016 08:49:34 GMT

Hi sunny,

The first one will be better to convert. This post is raised by my friend

Re: RE:Change if

tamilarasu — Fri, 05 Feb 2016 08:55:55 GMT

Try the below one,

if(MATCH_TYPE = 'VOD','Tier 1',
  if(MATCH_TYPE <> 'VOD' AND Match(NETWORK_TYPE,'Broadcast', 'Cable') AND Len(Trim(PROGRAM_NAME)) <> 0,
      if((TIMESHIFT_INDICATOR_ID >= 10 AND TIMESHIFT_INDICATOR_ID <= 16) And SAMPLE <> 0,
            'Tier 2','Tier 1')))
As Field_Name

Re: RE:Change if

jyothish8807 — Fri, 05 Feb 2016 08:59:59 GMT

Hi Suresh,

Qlikview have "switch case" function. You can find it in Help.

It might be helpful.

Regards

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 09:01:33 GMT

thnx KC will look into that till i get any updates frm experts.

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 09:03:09 GMT

thnx fr reply raj... wil chk nd update u if it wrks r nt.

Re: RE:Change if

Peter_Cammaert — Fri, 05 Feb 2016 09:05:27 GMT

In QlikView script, SWITCH... CASE is a control statement that is to be used outside of other statements like LOAD etc...

LOAD statements only accept functions, like IF() (this is a function, not a statement, although there is an IF control statement as well).

In a LOAD statement, you can use a Pick/Match combo to simplify IFs that test for individual values. In your last example, the last part is pretty useless because IMHO it tests for TIMESHIFT_INDICATOR_ID being equal to 10 AND 11 AND 12 AND 13..., all at the same time, which is obviously never the case. However, if you want to check whether a field is within a range of consecutive numerical values, do like:

IF ((TIMESHIFT_INDICATOR_ID >= 10) AND (TIMESHIFT_INDICATOR_ID <= 16) AND (SAMPLE <> 0),

'Tier 2','Tier 1')

Best,

Peter

Re: RE:Change if

jagan — Fri, 05 Feb 2016 09:07:10 GMT

Hi,

Case statement cannot be applied in Load statement, it should be applied outside Load statement like FOR, WHILE etc.

Regards,

Jagan.

Re: RE:Change if

jagan — Fri, 05 Feb 2016 09:08:30 GMT

Example from Qlikview help file.

Examples:

switch I

case 1

load '$(I): CASE 1' as case autogenerate 1;

case 2

load '$(I): CASE 2' as case autogenerate 1;

default

load '$(I): DEFAULT' as case autogenerate 1;

end switch

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 09:11:09 GMT

thnx jagan,and pcammaert

will try both of ur logic..

Re: RE:Change if

jagan — Fri, 05 Feb 2016 09:12:44 GMT

Hi,

You can fine tune this by

If(MATCH_TYPE = 'VOD','Tier 1',

If(MATCH_TYPE <> 'VOD' AND Match(NETWORK_TYPE, 'Broadcast', 'Cable') AND PROGRAM_NAME <>null,

AND SAMPLE <> 0 AND MATCH(TIMESHIFT_INDICATOR_ID, 10, 12, 13, 14, 15, 16), 'Tier 2', 'Tier 1'))

as field_name

Hope this helps you.

Regards,

jagan.

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 09:17:06 GMT

And one more info is...

if am usng with in Select statment not in load statmnt.

am usng inside Select statmnt how shud i write this query?

If(MATCH_TYPE='VOD', 'Tier 1',

If(NOT MATCH_TYPE(NETWORK_TYPE,'Broadcast','Cable') AND NOT IsNull(PROGRAM_NAME),

If(Num#(Left(TIMESHIFT_INDICATOR_ID,2)) >= 10 AND Num(Left(TIMESHIFT_INDICATOR_ID,2)) <= 16

AND Count(Distinct USERS_META_ID) <> 0,'Tier 2','Tier 1')

)

) as Field_name

Re: RE:Change if

Chanty4u — Fri, 05 Feb 2016 09:18:10 GMT

hi jagan.

This is not inside the load stmnt . it is related select stmnt. wit in that how can i write?