Verify the sequentiality of the data

[OrbytaQliker]

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!

[rwunderlich · Accepted solution]

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

[OrbytaQliker]

Thank you, Rob!

Your suggestion works perfectly for my need!