Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

palanimurugan
New Contributor II

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 II

Re: carry forward values

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

sureshqv
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

sureshqv
Esteemed Contributor III

Re: carry forward values

Not tested may be try this

 

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

Re: carry forward values

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

sureshqv
Esteemed Contributor III

Re: carry forward values

yes sunny

palanimurugan
New Contributor II

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 II

Re: carry forward values

Hi Sunny,

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

Community Browser