topic Re: Load Script IF statement Help in QlikView

Load Script IF statement Help

Anonymous — Mon, 21 Sep 2015 18:23:23 GMT

Hi All,

I’m trying to add an IF statement to my load script. What I’m
trying to do here is to rename the ‘Status’ if it meets this condition. For
example: (see below).

Temp:
Load * Inline
[
Name, Status
Apple, Red
Banana, Yellow
Orange, OK
Pear, Green
];

If(Status=(’Red’,‘Yellow’), Status=’Approved’

If(Status=(’Green’),’OK’,Status))

Many Thanks,

Frank

Re: Load Script IF statement Help

pamaxeed — Mon, 21 Sep 2015 18:26:08 GMT

You can do something like that:

Status:

Load Name,

Status,

if(Status = 'Red' or Status = 'Yellow', 'Approved', if(Status='Green', 'OK', Status)) as NewStatus

Resident Temp;

Cheers,

Patric

Re: Load Script IF statement Help

MK_QSL — Mon, 21 Sep 2015 18:30:36 GMT

Temp:

Load

If(Match(Status,'Red','Yellow'),'Approved',

If(Match(Status,'Green'),'OK', Status)) as NewStatus

Inline
[
Name, Status
Apple, Red
Banana, Yellow
Orange, OK
Pear, Green
];

Re: Load Script IF statement Help

Kushal_Chawda — Mon, 21 Sep 2015 18:32:15 GMT

try

Load *,If(match(Status,'Red','Yellow'),'Approved',if(Status='Green','Ok',Status)) as Field Inline
[
Name, Status
Apple, Red
Banana, Yellow
Orange, OK
Pear, Green
];

Re: Load Script IF statement Help

pamaxeed — Mon, 21 Sep 2015 18:33:40 GMT

Other possibility is to use a Mapping Load just like this:

MapToStatus:

Mapping LOAD * Inline [

Status, NewStatus

Red, Approved

Yellow, Approved

Green, OK

];

Temp:

Load * Inline

[

Name, Status

Apple, Red

Banana, Yellow

Orange, OK

Pear, Green

];

Status:

Load Name,

Status,

ApplyMap('MapToStatus', Status) as NewStatus

Resident Temp;

Drop Table Temp;

Re: Load Script IF statement Help

maxgro — Mon, 21 Sep 2015 18:54:55 GMT

one more

Temp:

Load

Name,

Status,

pick(WildMatch(Status, 'Red', 'Yellow', 'Green', '*'), 'Approved', 'Approved', 'OK', Status) as NewStatus

Inline

[

Name, Status

Apple, Red

Banana, Yellow

Orange, OK

Pear, Green

];

Re: Load Script IF statement Help

Anonymous — Mon, 21 Sep 2015 19:40:12 GMT

Hi All, Thanks for all the replies. All works like a charm!!!

Frank

Re: Load Script IF statement Help

Anonymous — Mon, 21 Sep 2015 19:45:13 GMT

Patric,

Yours works well with AND as well. I added a DATE fied where one is blank and wanted to show as "Outstanding"

Name, Status, Date

Apple, Red,1/1/2015

Banana, Yellow,2/2/2015

Orange, OK

Pear, Green,3/2/2015

Load Name,

      Status,

      if(Status<>'' and Date='', 'Outstanding',

      if(Status = 'Red' or Status = 'Yellow', 'Approved',

      if(Status='Green', 'OK', Status))) as NewStatus

Resident Temp;

Re: Load Script IF statement Help

Anonymous — Mon, 21 Sep 2015 19:50:40 GMT

Manish,

Can it add AND to the IF statement? not sure how it works with "Match"

such as:

If Name<>blank AND Date=blank, "Outstanding"

I added something like this to your If statement and it works.

IF ([Status]<>'' and IsNull([Date]) or [Date]='', 'Outstanding',

If(Match(Status,'Red','Yellow'),'Approved',

If(Match(Status,'Green'),'OK', Status)) as NewStatus

Name, Status, Date

Apple, Red,1/1/2015

Banana, Yellow,2/2/2015

Orange, OK

Pear, Green,3/2/2015