topic Re: Script Help - if then doing something in QlikView

Script Help - if then doing something

Anonymous — Fri, 15 Jan 2016 09:48:23 GMT

Hi all,

Assume I have the following data:

ID	Status	TDate	Amt
A01	Open	15/02/2015	35
A02	Open	13/02/2015	40
A03	Open	28/02/2015	25
A04	Open	20/02/2015	99
A01	Close	14/03/2015
A02	Open	18/03/2015	79
A04	Close	18/03/2015

Now, I need to do if the status is close then I need take the Amt from last month, else take Amt as a Close_Amt fro the particular ID(see following result).

Is it possible to make it at script? How can I come out the result at scripting?

ID	Status	TDate	Amt	Close_Amt
A01	Open	15/02/2015	35	35
A02	Open	13/02/2015	40	40
A03	Open	28/02/2015	25	25
A04	Open	20/02/2015	99	99
A01	Close	14/03/2015		35
A02	Open	18/03/2015	79	79
A04	Close	18/03/2015		99

Re: Script Help - if then doing something

Fri, 15 Jan 2016 12:40:40 GMT

Yes it is possible in the script. Make sure that it is sorted accordingly.
Just made this mockup for you,

Include_PC:

Load * Inline [

Date, IncludePC, Navn, Amt

40000, 6042, MAre, 100

40500, 6020, Hordaland,200

41000, 6042, MAre, ];

PC_MAP:

load

Navn,

IncludePC,

Amt as Amt_T,

Date,

if(isnull(Amt) or trim(Amt)='',peek('Amt'),Amt) as Amt

resident Include_PC order by IncludePC,Navn,Date ASC;

drop table Include_PC;

You can either just rename the fields back in a resident, rename field or something like that as this produces 'Amt' and Amt_T.

Or expand the If " for Close amt into something like

PC_MAP:

load

Navn,

IncludePC,

Amt,

Date,

if(isnull(Amt) or trim(Amt)='',if(isnull(peek('Amt')) or trim(peek('Amt'))='', peek('Close_Amt'), peek('Amt')) ,Amt) as Close_Amt

//if(isnull(Amt) or trim(Amt)='',peek('Amt'),Amt) as Amt

resident Include_PC order by IncludePC,Navn,Date ASC;

drop table Include_PC;

Re: Script Help - if then doing something

sunny_talwar — Fri, 15 Jan 2016 12:55:16 GMT

Try this:

SET DateFormat='DD/MM/YYYY';

Table:

LOAD ID,

Status,

TDate,

Amt

FROM

[https://community.qlik.com/thread/200253]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(Status = 'Open', Amt,

If(ID = Peek('ID'), Peek('Close_Amt'))) as Close_Amt

Resident Table

Order By ID, TDate;

DROP Table Table;

Re: Script Help - if then doing something

Fri, 15 Jan 2016 13:08:57 GMT

Ops, my last suggestion forgot a peek on ID/IncludePC in the if statement.

Include_PC:

Load * Inline [

Date, IncludePC, Navn, Amt

40000, 6042, MAre, 100

40500, 6020, Hordaland,200

41000, 6042, MAre, ];

Test:

load

Navn,

IncludePC,

Amt,

Date,

if(isnull(Amt) or trim(Amt)='' and IncludePC=peek('IncludePC') ,if(isnull(peek('Amt')) or trim(peek('Amt'))='', peek('Close_Amt'), peek('Amt')) ,Amt) as Close_Amt

resident Include_PC order by IncludePC,Navn,Date ASC;

Re: Script Help - if then doing something

Fri, 15 Jan 2016 14:06:28 GMT

Hey,

Try This.

set NullInterpret='';

TAB2:

//load ID,Status,TDate,Amt,if(Status='Open',Amt,)

LOAD * INLINE [

ID, Status, TDate, Amt

A01, Open, 15/02/2015, 35

A02, Open, 13/02/2015, 40

A03, Open, 28/02/2015, 25

A04, Open, 20/02/2015, 99

A01, Close, 14/03/2015,

A02, Open, 18/03/2015, 79

A04, Close, 18/03/2015,

];

TAB3:

load *,1

resident TAB2

order by ID,TDate desc;

load *,if(isnull(Amt),peek('Amt',-1),Amt) as NAmt

Resident TAB3;

drop table TAB2,TAB3;

drop field 1;

Re: Script Help - if then doing something

Anonymous — Fri, 15 Jan 2016 14:18:45 GMT

Hi Sunny,

I' sorry.My user gt slightly change the requirement. He wan only show the last month amt when then status equal to close. Like following. Gt any idea to do this?

ID	Status	TDate	Amt	Close_Amt
A01	Open	15/02/2015	35
A02	Open	13/02/2015	40
A03	Open	28/02/2015	25
A04	Open	20/02/2015	99
A01	Close	14/03/2015		35
A02	Open	18/03/2015	79
A04	Close	18/03/2015		99

Re: Script Help - if then doing something

Fri, 15 Jan 2016 14:42:50 GMT

Hi Wong, couple of questions before answer your question ?

In your source data, do you have more than one row for previous month & current Month on ID and status combination? If Yes, you need write complex script to get out of this.

Re: Script Help - if then doing something

sunny_talwar — Fri, 15 Jan 2016 17:55:01 GMT

Try this:

Table:

LOAD ID,

Status,

TDate,

Amt

FROM

[https://community.qlik.com/thread/200253]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(Status = 'Close' and ID = Peek('ID'), Peek('Amt')) as Close_Amt

Resident Table

Order By ID, TDate;

DROP Table Table;

Re: Script Help - if then doing something

Anonymous — Tue, 19 Jan 2016 08:09:59 GMT

Hi Dathu,

There has only one record for previous month and current month on ID and status. I fond the answer to to this.

Thanks for help.