topic Re: Scenario using script in QlikView

Scenario using script

Thu, 12 Jun 2014 17:45:35 GMT

I have a source with

Date	Outbal
1/1/2014	100
4/1/2014	400
2/1/2014	500
3/1/2014	200
5/1/2014	150
6/1/2014	450
7/1/2014	300

And I need a Output Like this:

Date	Outbal	Inbal
1/1/2014	100	300(last value)
2/1/2014	500	100
3/1/2014	200	500
4/1/2014	400	200
5/1/2014	150	400
6/1/2014	450	150
7/1/2014	300	450

Re: Scenario using script

its_anandrjs — Thu, 12 Jun 2014 17:57:57 GMT

Try to load your table like this way

Source:

LOAD Date(Date,'D/M/YYYY') as Date,Outbal;

LOAD * Inline

[

Date, Outbal

1/1/2014, 100

2/1/2014, 500

3/1/2014, 200

4/1/2014, 400

5/1/2014, 150

6/1/2014, 450

7/1/2014, 300

];

NoConcatenate

NewTable:

LOAD

Date,

Outbal,

if(RowNo() = 1,Peek('Outbal',-1,'Source') ,Previous(Outbal)) as Inbal

Resident Source;

DROP Table Source;

Re: Scenario using script

Thu, 12 Jun 2014 18:13:25 GMT

Hi Anand,

Thanks for your answer but in source we have date values not in a order so we need perform order by with date.plz find the source.

Re: Scenario using script

its_anandrjs — Thu, 12 Jun 2014 18:47:43 GMT

Then try with this and creating order by for Date

tmpSource:

LOAD * Inline

[

Date, Outbal

1/1/2014, 100

2/1/2014, 500

3/1/2014, 200

4/1/2014, 400

5/1/2014, 150

6/1/2014, 450

7/1/2014, 300

];

NoConcatenate

NewSource:

LOAD Date(Date,'D/M/YYYY') as Date,Outbal Resident tmpSource Order By Date;

DROP Table tmpSource;

NewTable:

LOAD

Date,

Outbal,

if(RowNo() = 1,Peek('Outbal',-1,'Source') ,Previous(Outbal)) as Inbal

Resident NewSource;

DROP Table NewSource;