topic Re: auto generate value using script in QlikView

auto generate value using script

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

Hi all,

I am facing a problem in script level.

Which is i need to create auto generate value using script.

my example is

Item No	Item id	Date	Value	value2
2013	101	01/01/2017	0	100
2013	101	02/01/2017	0	100
2013	101	03/01/2017	100	100
2013	101	04/01/2017	0	100
2013	101	05/01/2017	90	90
2013	101	06/01/2017	0	90
2013	102	01/01/2017	0	200
2013	102	02/01/2017	200	200
2013	102	03/01/2017	0	200
2013	102	04/01/2017	170	170
2013	102	05/01/2017	0	170

please see my table i have 3 dimension which is item no , item id and date,

and also a measure which name is value i need like value2 measure using script.

If you have any solution then please suggest me.

Thanks and regards

Swarnendu

Re: auto generate value using script

YoussefBelloum — Tue, 02 Jan 2018 09:06:56 GMT

Hi,

what is the rules to calculate the Value 2 ?

Re: auto generate value using script

swarnendu — Tue, 02 Jan 2018 09:12:09 GMT

i used like this but its not working properly.

If(Item_no<>peek(Item_no) and Item_Id<>peek(Item_Id) and Date<>peek(Date),Value,Peek(Value)) as Value,

I need to generate the value filed like value 2 field.

Re: auto generate value using script

YoussefBelloum — Tue, 02 Jan 2018 09:27:44 GMT

your script is working fine here, the Peek works fine, it will always gives you the ELSE part which is the Peek(Value) because the condition is never met, Item No is always the same on the table you gave

Re: auto generate value using script

swarnendu — Tue, 02 Jan 2018 09:32:02 GMT

I know this problem but have you any idea how i can do it?

if you have any idea then please share.

Regards

swarnendu

Re: auto generate value using script

YoussefBelloum — Tue, 02 Jan 2018 09:37:18 GMT

your calculation rules for Value2 field is simply wrong..

Try to define correct rules that can be applied to your data

Re: auto generate value using script

swarnendu — Tue, 02 Jan 2018 09:41:29 GMT

any suggestion ?

Re: auto generate value using script

YoussefBelloum — Tue, 02 Jan 2018 09:47:54 GMT

I really can't tell you how to calculate your field..

but the only peek that make sense here is the Peek(Date), because there are some Dates presents on differents lines for different Item_id's.

But the question is how did you calculate the values present on the VALUE2 column ??

Re: auto generate value using script

qliksus — Tue, 02 Jan 2018 10:35:47 GMT

Maybe something like the below

A1:
load

ItemNo,
Itemid, Date, Value , if(Peek(Itemid)=Itemid and Value=0, Peek(Value),Value) as Values

Resident A
order by ItemNo,Itemid,Date
;

A2:
load

ItemNo,
Itemid, Date, Value , if(Peek(Itemid)=Itemid and Values=0, Peek(Valuesfinal),Values) as Valuesfinal

Resident A1
order by ItemNo,Itemid,Date desc
;

DROP Table A,A1 ;

Re: auto generate value using script

swarnendu — Tue, 02 Jan 2018 12:39:26 GMT

Can you please share your .qvw file

Re: auto generate value using script

qliksus — Wed, 03 Jan 2018 12:14:20 GMT

Attachment is restriced in my environment but here is the script

Script:

A:
LOAD * INLINE [

    ItemNo, Itemid, Date, Value, value2
    2013, 101, 01/01/2017, 0, 100
    2013, 101, 02/01/2017, 0, 100
    2013, 101, 03/01/2017, 100, 100
    2013, 101, 04/01/2017, 0, 100
    2013, 101, 05/01/2017, 90, 90
    2013, 101, 06/01/2017, 0, 90
    2013, 102, 01/01/2017, 0, 200
    2013, 102, 02/01/2017, 200, 200
    2013, 102, 03/01/2017, 0, 200
    2013, 102, 04/01/2017, 170, 170
    2013, 102, 05/01/2017, 0, 170
];

A1:
load

ItemNo,
Itemid, Date, Value , if(Peek(Itemid)=Itemid and Value=0, Peek(Value),Value) as Values

Resident A
order by ItemNo,Itemid,Date
;

A2:
load

ItemNo,
Itemid, Date, Value , if(Peek(Itemid)=Itemid and Values=0, Peek(Valuesfinal),Values) as Valuesfinal

Resident A1
order by ItemNo,Itemid,Date desc
;

DROP Table A,A1 ;

Front End:

Re: auto generate value using script

swarnendu — Wed, 03 Jan 2018 12:36:30 GMT

Hello brother please look at this discussion hope you can help me.

https://community.qlik.com/message/1410521#1410521