Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
The requirement is :
I have 4 columns in my transactional table , C1,C2,C3,C4
If on any particular day I do not receive a value for either of the columns,I need to fetch corresponding value from the previous row.
Especially, on weekends(Saturday and Sunday) I do not receive data for any of the columns, so I need to populate previous day(Friday) values.
Plz help.
Thanks
Try this:
Table:
LOAD * Inline [
Col1, Col2, Col3, Col4
3/3/2016, 12, 13, 14
4/3/2016, , 23, 24
7/3/2016, 32, ,
8/3/2016, 42, 43, 44
];
Join (Table)
LOAD Date(Min + IterNo() - 1) as Col1
While Min + IterNo() - 1 < Max;
LOAD Max(Col1) as Max,
Min(Col1) as Min
Resident Table;
FinalTable:
NoConcatenate
LOAD Col1,
If(Len(Trim(Col2)) = 0, Peek('Col2'), Col2) as Col2,
If(Len(Trim(Col3)) = 0, Peek('Col3'), Col3) as Col3,
If(Len(Trim(Col4)) = 0, Peek('Col4'), Col4) as Col4
Resident Table
Order By Col1;
DROP Table Table;
If you have the dates already available in your database you can use Peek()/Previous() function to do it. Do you have a sample where it can be demonstrated?
Please go through https://community.qlik.com/docs/DOC-7142
HTH
Vikas
A sample data set is :
Required output is:
We usually do not receive data for weekends/public holidays,still we need to populate as mentioned above
Try this:
Table:
LOAD * Inline [
Col1, Col2, Col3, Col4
3/3/2016, 12, 13, 14
4/3/2016, , 23, 24
7/3/2016, 32, ,
8/3/2016, 42, 43, 44
];
Join (Table)
LOAD Date(Min + IterNo() - 1) as Col1
While Min + IterNo() - 1 < Max;
LOAD Max(Col1) as Max,
Min(Col1) as Min
Resident Table;
FinalTable:
NoConcatenate
LOAD Col1,
If(Len(Trim(Col2)) = 0, Peek('Col2'), Col2) as Col2,
If(Len(Trim(Col3)) = 0, Peek('Col3'), Col3) as Col3,
If(Len(Trim(Col4)) = 0, Peek('Col4'), Col4) as Col4
Resident Table
Order By Col1;
DROP Table Table;
Hi,
I have four columns
Date Category Sub-Category Value
1.1.2017 A a 200
1.1.2017 B b 300
1.1.2017 C c 200
2.1.2017 A d 500
2.1.2017 B e 400
My Output is:
I want to generate the line of C Category with previous value.....
help me...
Thank You
So you want to add another row for 2.1.2017 with category as C and Value as 200? What will be the Sub-Category? c also?
yes