Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a problem where i need to look back into a set of data to bring back the latest result
The data looks like the following
ID Number | Station | Start Station | End Station | Journey |
---|---|---|---|---|
101 | Station 1 | Station 1 | - | |
102 | Station 2 | - | - | |
103 | Station 3 | - | - | |
104 | Station 4 | - | - | |
105 | Station 5 | - | - | |
106 | Station 6 | - | - | |
107 | Station 7 | - | Station 7 | |
106 | Station 6 | Station 6 | - | |
105 | Station 5 | - | - | |
104 | Station 4 | - | Station 4 |
I need to be able to fill in the blanks where i would look back and get the start and end of each journey. The Journey would be a combination of the two.
This would be like the following:
ID Number | Station | Start Station | End Station | Journey |
---|---|---|---|---|
101 | Station 1 | Station 1 | Station 7 | Station 1 - Station 7 |
102 | Station 2 | Station 1 | Station 7 | Station 1 - Station 7 |
103 | Station 3 | Station 1 | Station 7 | Station 1 - Station 7 |
104 | Station 4 | Station 1 | Station 7 | Station 1 - Station 7 |
105 | Station 5 | Station 1 | Station 7 | Station 1 - Station 7 |
106 | Station 6 | Station 1 | Station 7 | Station 1 - Station 7 |
107 | Station 7 | Station 1 | Station 7 | Station 1 - Station 7 |
106 | Station 6 | Station 6 | Station 4 | Station 6 - Station 4 |
105 | Station 5 | Station 6 | Station 4 | Station 6 - Station 4 |
104 | Station 4 | Station 6 | Station 4 | Station 6 - Station 4 |
I was going to use a peek function but would need to look back only as far as the next entry. Is there a way to do that?
Many Thanks,
Simon
Hey Simon,
Tried with peek function.
Test:
LOAD [ID Number],
Station,
[Start Station],
[End Station],
Journey
FROM
C:\Users\nguntur\Desktop\peektillnull.xlsx
(ooxml, embedded labels, table is Sheet1);
STORE Test into Test.qvd(qvd);
LOAD
*,
if(IsNull([Start Station]),Peek([NewStation]),[Start Station]) as NewStation
Resident Test;
DROP Table Test;
When you load the first value, park it in a field "NewStation" so that later when [Start Station] is null, it will get the value from "NewStation"
Thanks,
Naresh@
Simon
Have a look at this pdf Generating missing data.pdf by HIC from this technical brief Generating Missing Data In QlikView.
Read pages 6 & 7 and you may well find the solution you need.
Best Regards, Bill
Hey Simon,
Tried with peek function.
Test:
LOAD [ID Number],
Station,
[Start Station],
[End Station],
Journey
FROM
C:\Users\nguntur\Desktop\peektillnull.xlsx
(ooxml, embedded labels, table is Sheet1);
STORE Test into Test.qvd(qvd);
LOAD
*,
if(IsNull([Start Station]),Peek([NewStation]),[Start Station]) as NewStation
Resident Test;
DROP Table Test;
When you load the first value, park it in a field "NewStation" so that later when [Start Station] is null, it will get the value from "NewStation"
Thanks,
Naresh@
Try this:
WWW:
LOAD * Inline [
ID_Number, Station, Start_Station, End_Station, Journey
101, Station 1, Station 1, -, -
102, Station 2, -, -, -
103, Station 3, -, -, -
104, Station 4, -, -, -
105, Station 5, -, -, -
106, Station 6, -, -, -
107, Station 7, -, Station 7, -
106, Station 6, Station 6, -, -
105, Station 5, -, -, -
104, Station 4, -, Station 4, -
];
W1:
NoConcatenate
LOAD
*,
if(Start_Station = '-',Peek([NewStation]),Start_Station) as NewStation,
RowNo() As RowId
Resident WWW;
DROP Table WWW;
WWW:
NoConcatenate
LOAD
*,
if(End_Station = '-',Peek(NewStation1),End_Station) as NewStation1
Resident W1
Order by RowId desc;
DROP Table W1;
Final:
NoConcatenate
LOAD
*,
NewStation & ' - ' & NewStation1 As JourneyNew
Resident WWW
Order By RowId;
DROP Table WWW;
DROP Fields Start_Station, End_Station, Journey;
HI All,
Thank you for all your help. I have just tried the code Naresh supplied and it work for me. It Peeks back and brings back the instance i need.
Thanks Again,
Simon