Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
let's assume that i have the following data
[
ID,Station,Status
1,1,'ok'
1,2,'ok'
1,3,'no'
2,1,'no'
2,2,'no'
2,3,'no'
3,1,'no'
3,2,'ok'
3,3,'no'
]
For each id, there will be 3 rows in the data, which identify the the status of its three stations.
And these stations should be passed sequentially, so, the first is 1, then 2 and finally 3.
Initially, all station are set to no in their status, and it will be changed in ok once it's completed.
My question is:
How can i verify in load script whether the sequentiality of station is respected. So for each id i have, i want to know whether if is respecting the sequentiality .
The output of this case would be:
[
ID, respected
1,'yes'
2,'yes'
3,'no'
]
We can notice that the id 3 is not respecting that, because it passed the second station without completing the first.
I'll really appreciate your support.
Thank you!
A way to express this rule is that "ok" should never follow "no" in the sequence. How about:
Data:
Load *
Inline [
ID,Station,Status
1,1,'ok'
1,2,'ok'
1,3,'no'
2,1,'no'
2,2,'no'
2,3,'no'
3,1,'no'
3,2,'ok'
3,3,'no'
4,1,'ok'
4,2,'no'
4,3,'ok'
];
Validate:
LOAD
*,
If(
index(mid(StatusString, index(StatusString, 'no')), 'ok') = 0
,'yes', 'no') as Respected
;
LOAD
ID,
COncat(Status, '', Station) as StatusString
Resident Data
Group By ID;
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
A way to express this rule is that "ok" should never follow "no" in the sequence. How about:
Data:
Load *
Inline [
ID,Station,Status
1,1,'ok'
1,2,'ok'
1,3,'no'
2,1,'no'
2,2,'no'
2,3,'no'
3,1,'no'
3,2,'ok'
3,3,'no'
4,1,'ok'
4,2,'no'
4,3,'ok'
];
Validate:
LOAD
*,
If(
index(mid(StatusString, index(StatusString, 'no')), 'ok') = 0
,'yes', 'no') as Respected
;
LOAD
ID,
COncat(Status, '', Station) as StatusString
Resident Data
Group By ID;
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
Thank you, Rob!
Your suggestion works perfectly for my need!