topic Re: How to get previous available data in QlikView

How to get previous available data

Tue, 03 Jun 2014 10:11:12 GMT

Hello All,

I have data in table as below:

Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f, 2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 10, 90

Now here, in the above table i have data till aug (i.e month08). and no data for 9.

so same value of month8 should be forwarded to 9.

similarly, value of month 10 should be forwarded to 11, 12.

Re: How to get previous available data

its_anandrjs — Tue, 03 Jun 2014 10:18:13 GMT

What will be for other fields like Year,Field1.Month and Field2 that will also peek the previous values. Then use the previous and peek functions.

Re: How to get previous available data

Tue, 03 Jun 2014 10:26:27 GMT

Hi Anand,

I have managed to fill the empty rows as below for the key of (Field1, Field2, Year)

Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f, 2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 09, 0

f1h, f2h, 2013, 10, 90

f1h, f2h, 2013, 11, 0

f1h, f2h, 2013, 12, 0

~Thanks & Regards

Re: Re: How to get previous available data

tresB — Tue, 03 Jun 2014 10:28:49 GMT

Data:
Load IterNo() as Month AutoGenerate 1 While IterNo()<=12;
Left Join
Load * Inline [
Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f, 2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 10, 90
];
NoConcatenate
Final:

Load
If(IsNull(Field1), Peek(Field1), Field1) as Field1,
If(IsNull(Field2), Peek(Field2), Field2) as Field2,
If(IsNull(Year), Peek(Year), Year) as Year,
If(IsNull(Value), Peek(Value), Value) as Value,
Month;

Load
*
Resident Data Order By Month;

Drop Table Data;

Re: How to get previous available data

its_anandrjs — Tue, 03 Jun 2014 11:23:27 GMT

Then load your table with use of peek functions

temp:

LOAD * Inline

[

Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 01, 10

f1b, f2b, 2013, 02, 20

f1c, f2c, 2013, 03, 30

f1d, f2d, 2013, 04, 40

f1e, f2e, 2013, 05, 50

f1f, f2f, 2013, 06, 60

f1g, f2g, 2013, 07, 70

f1h, f2h, 2013, 08, 80

f1h, f2h, 2013, 10, 90

];

Join

LOAD * Inline

[

Month

];

NoConcatenate

Final:

LOAD

if(IsNull(Field1) = -1, Peek(Field1),Field1) as Field1,

if(IsNull(Field2) = -1, Peek(Field2),Field2) as Field2,

if(IsNull(Month) = -1, Peek(Month),Month) as Month,

if(IsNull(Year) = -1, Peek(Year),Year) as Year,

if(IsNull(Value) = -1,Peek(Value),Value) as Value Resident temp Order By Month Asc;

DROP Table temp;

Re: Re: How to get previous available data

Tue, 03 Jun 2014 11:42:00 GMT

Hi tresesco,

Load * Inline [
Field1, Field2, Year, Month, Value
f1a, f2a, 2013, 01, 10
f1a, f2a, 2013, 02, 20
f1a, f2a, 2013, 03, 30
f1a, f2a, 2013, 04, 40
f1a, f2a, 2013, 05, 50
f1a, f2a, 2013, 06, 60
f1a, f2a, 2013, 07, 70
f1a, f2a, 2013, 08, 80
f1a, f2a, 2013, 10, 90
f1b, f2b, 2013, 01, 10
f1b, f2b, 2013, 02, 20
f1b, f2b, 2013, 03, 30
f1b, f2b, 2013, 04, 40
f1b, f2b, 2013, 05, 50
f1b, f2b, 2013, 06, 60
f1b, f2b, 2013, 07, 70
f1b, f2b, 2013, 08, 80
f1b, f2b, 2013, 10, 90
];

can you try with this data

Re: How to get previous available data

MayilVahanan — Tue, 03 Jun 2014 11:56:51 GMT

Try with this

Data:

Load * Inline [

Field1, Field2, Year, Month, Value

f1a, f2a, 2013, 1, 10

f1a, f2a, 2013, 2, 20

f1a, f2a, 2013, 3, 30

f1a, f2a, 2013, 4, 40

f1a, f2a, 2013, 5, 50

f1a, f2a, 2013, 6, 60

f1a, f2a, 2013, 7, 70

f1a, f2a, 2013, 8, 80

f1a, f2a, 2013, 10, 90

f1b, f2b, 2013, 1, 10

f1b, f2b, 2013, 2, 20

f1b, f2b, 2013, 3, 30

f1b, f2b, 2013, 4, 40

f1b, f2b, 2013, 5, 50

f1b, f2b, 2013, 6, 60

f1b, f2b, 2013, 7, 70

f1b, f2b, 2013, 8, 80

f1b, f2b, 2013, 10, 90

];

Temp:

Load IterNo() as Month AutoGenerate 1 While IterNo()<=12;

Left Join

Load Field1 Resident Data;

Right Join(Data)

LOAD * Resident Temp;

NoConcatenate

Final:

Load

If(IsNull(Field1), Peek(Field1), Field1) as Field1,

If(IsNull(Field2), Peek(Field2), Field2) as Field2,

If(IsNull(Year), Peek(Year), Year) as Year,

If(IsNull(Value), Peek(Value), Value) as Value,

Month;

Load

Resident Data Order By Month;

Drop Table Data, Temp;

Re: How to get previous available data

Tue, 03 Jun 2014 12:05:23 GMT

No it is not giving correct output.

Re: Re: How to get previous available data

MayilVahanan — Tue, 03 Jun 2014 12:09:17 GMT

PFA

Can you say, what is the expected output?

Re: Re: How to get previous available data

Tue, 03 Jun 2014 12:17:54 GMT

The highlighted data is wrong. it should f1a, f1a

Re: Re: Re: How to get previous available data

MayilVahanan — Tue, 03 Jun 2014 12:22:42 GMT

Try with this

PFA

Re: How to get previous available data

tresB — Tue, 03 Jun 2014 12:26:47 GMT

If I go with Mayil's way, it would require a small correction:

.....

Temp:

Load IterNo() as Month AutoGenerate 1 While IterNo()<=12;

Left Join

Load Field1, Field2 , Year Resident Data; // add Field2, Year for generic solution

...........

Update: Yes, Oredr By has to so as well.