Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
palanimurugan
New Contributor III

carry forward values

Dear Experts,

I need to carry forward the value, if the value is zero.

Attached Snapshot for your reference.

For  Example, In third row the value has Zero.So I have to bring and set the previous values(i.e 45).

Similarly i have set for all values. Kindly help me to fix this.

Thanks!!!

   

iddateValue
16/4/201445
210/3/20140
310/16/20144
1211/7/20142
211/13/201423
1212/29/20144
11/12/20150
12/26/20152
123/19/20150
34/14/20150
24/20/20150
15/22/20153
237/17/20151
1 Solution

Accepted Solutions

Re: carry forward values

I guess add a second condition

If(id = Peek(id) and Value = 0, Peek('NewValue'), Value) as NewValue

and sort by first id and then Date

Order By id, Date;

18 Replies

Re: carry forward values

This is something you are hoping to fix in the in the script?

palanimurugan
New Contributor III

Re: carry forward values

Yes, Sunny... I need to fix this while loading the data.

Chanty4u
Esteemed Contributor III

Re: carry forward values

Hi

1 : in script windows, go on table file load,

2 : load your file, but don't apply now -> next,-> enable transformation step

3 : go in fill tab, and choose, -> fill -> target column 2 -> cell condition (is empty) // above

NEXT NEXT FINISH

It will fill empty rows with above value

Chanty4u
Esteemed Contributor III

Re: carry forward values

Not tested may be try this

 

if(IsNull([Value]),Peek([Value]),[Value]) as New;

Highlighted

Re: carry forward values

You mean If(Value = 0,....)?

Chanty4u
Esteemed Contributor III

Re: carry forward values

yes sunny

palanimurugan
New Contributor III

Re: carry forward values

Dear Sunny,

I have tried my end but it is not working for me.

Below is the script.

test:

LOAD id,

     date,

     Value

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Replace(2, top, StrCnd(equal, '0'))

));

Sample Data

   

iddateValue
16/4/201445
210/3/20140
310/16/20144
1211/7/20142
211/13/201423
1212/29/20144
11/12/20150
12/26/20152
123/19/20150
34/14/20150
24/20/20150
15/22/20153
237/17/20151
239/25/20150
210/2/201512
111/11/20154

Re: carry forward values

May be this

Table:

LOAD * INLINE [

    id, date, Value

    1, 6/4/2014, 45

    2, 10/3/2014, 0

    3, 10/16/2014, 4

    12, 11/7/2014, 2

    2, 11/13/2014, 23

    12, 12/29/2014, 4

    1, 1/12/2015, 0

    1, 2/26/2015, 2

    12, 3/19/2015, 0

    3, 4/14/2015, 0

    2, 4/20/2015, 0

    1, 5/22/2015, 3

    23, 7/17/2015, 1

    23, 9/25/2015, 0

    2, 10/2/2015, 12

    1, 11/11/2015, 4

];


FinalTable:

LOAD id,

date,

Value,

If(Value = 0, Peek('NewValue'), Value) as NewValue

Resident Table

Order By date;


DROP Table Table;

Capture.PNG

palanimurugan
New Contributor III

Re: carry forward values

Hi Sunny,

Great thanks!!.. this is working fine....