Loop or something else?

[JuMacedo]

Hi,

I'm new to Qlik and I don't know all possible functions to be used in scripts. So I thought about describing my problem and check if i get some ideas on how to solve it.

I'm thinking maybe a Loop would solve it, but I haven't quite understood how to use it in scripts.

I have a table that looks like this:

Trip	Train Station	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
1	Station1	Yes	Yes	Yes	Yes	Yes	No	No
1	Station2	Yes	Yes	Yes	Yes	Yes	No	No
1	Station3	Yes	Yes	Yes	Yes	Yes	No	No
1	Station4	Yes	Yes	Yes	Yes	Yes	No	No
1	Station5	No	No	No	No	Yes	Yes	Yes
1	Station6	No	No	No	No	Yes	Yes	Yes
1	Station7	Yes	Yes	Yes	Yes	Yes	No	No
1	Station8	Yes	Yes	Yes	Yes	Yes	No	No
1	Station9	Yes	Yes	Yes	Yes	Yes	No	No

For every trip id, I need to check if a train goes from every station at least 5 out of 7 days in a week. A trip is only valid if a train goes from every station 5 out of 7 days per week (from monday to sunday).

Then as a result I would I have a new table that will aggregate data and show only the first station and the last and with an extra column indication a number for the availability of that trip, for example if the trip is valid it would show number 1, if the trip is not valid it would show number 0.

For example:

Trip	Start Station	End Station	Availability
1	Station1	Station8	1

How to achieve this? My idea here is that I have to iterate in every line of a trip and check all columns for weekdays...but is there a better way here?

I'm workin on Qlik Sense and using the script to load and transform data.

I'm thinking maybe a loop would solve the problem, but what would be the correct sintax? And is there any other function that would do the job?

[LRuCelver]

Small hint regarding the loop: Check out Crosstables

One question regarding the availability logic:
Do you need to check for every station whether that station is operated at least 5 days of the week, or do you need to check for every day, whether all stations are operated on that day?

[LRuCelver · Accepted solution]

Here is a small example how you could calculate the availability. I've assumed that you need to check for every day, whether all stations are operated on that day.

//	Loading the Data
//	I've added a new field for the order the stations are in and removed the 2nd row for Station4, assuming it to be a mistake.
//	Using a crosstable we can transform the table so that the weekdays become their own field.
Operate_Schedule:
CrossTable(Weekday, Operates_Text, 3) Load * Inline [
Trip	Train Station	Station Order	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
1	Station1	1	Yes	Yes	Yes	Yes	Yes	No	No
1	Station2	2	Yes	Yes	Yes	Yes	Yes	No	No
1	Station3	3	Yes	Yes	Yes	Yes	Yes	No	No
1	Station4	4	Yes	Yes	Yes	Yes	Yes	No	No
1	Station5	5	No	No	No	No	Yes	Yes	Yes
1	Station6	6	Yes	Yes	Yes	Yes	Yes	No	No
1	Station7	7	Yes	Yes	Yes	Yes	Yes	No	No
1	Station8	8	Yes	Yes	Yes	Yes	Yes	No	No
] (delimiter is '	');

//	Replace the Yes/No Text with a Dual Value for easier Salculations
Left Join Load
	Dual(Operates_Text, Operates_Num) as Operates
Inline [
	Operates_Text, Operates_Num
    Yes, 1
    No, 0
];
Drop Field Operates_Text;


//	Calculate the Availabity
//	I've used a Preceding Load here. The Load Statements are evaluated from the bottom up, passing the Result up as the Input to the next Load Statement.
//	This could be compacted further, but it should be easier to understand this way.
Trip_Availability:
NoConcatenate Load
	Trip,
    If(Days_with_all_Stations_operated >= 5, 1, 0) as Availability;
Load
	Trip,
    Sum(All_Stations_operated) as Days_with_all_Stations_operated
Group By Trip;
Load
	Trip,
    Weekday,
	If(Stations_operated = Number_of_Stations, 1, 0) as All_Stations_operated;
Load
	Trip,
    Weekday,
    Count("Train Station") as Number_of_Stations,
    Sum(Operates) as Stations_operated
Resident Operate_Schedule
Group By Trip, Weekday;

Since I've added the Station Order field, you can use the FirstSortedValue function to get the start and end stations:

Start: FirstSortedValue(distinct [Train Station], [Station Order])

End: FirstSortedValue(distinct [Train Station], -[Station Order])

[pravinboniface]

I believe you can do all of this in the UI with the following expressions

Start Station

Minstring([Train Station])

End Station

Maxstring([Train Station])

Availability

if (min(aggr (SubStringCount(Monday&Tuesday&Wednesday&Thursday&Friday&Saturday&Sunday,'Yes'), [Train Station]))>=5,1,0)

What I am doing here is for each train station, I concatenate the values from each day of the week and count how many 'Yes' are there.  By using the aggregation, I check for each station and check for the station with the minimum number of days.  If any station has less than 5 'Yes', then availability is 0 else we set to 1

[JuMacedo]

Hi!

I'll try the solution and let you know whether it works. I might try it just next week though.
Thanks for pointing out the error on station 4, I have corrected it now.

[JuMacedo]

Hi,

Interesting, I'll try that also and let you know whether it works. Just one question... the UI you mention is when working with the visualization? Then the expression would be when creating a new dimension / column?

[pravinboniface]

Yes, I am referring to the table visualization on the front end. Trip would be a dimension ant the other three are the measures I have provided above.

[pravinboniface]

@JuMacedo Did this work for you?

[JuMacedo]

@LRuCelver 
Sorry the delay, I just work 50% of the time with this project which means I have to divide my week. I'll work with this project today, so I'll test it today and get back to you.
I'm actually right now going through what you did and trying to understand it. 

I'm trying to understand this CrossTable function, I executed just the part of the code where you used this function and I see that it transposes all columns with weekdays into one column, but the result looks like this:

This repeats 8 times, for every station. But is this correct? Why does it input a number on Monday? and then it seems like it shuffles the fields ahead. For example, Saturday in this example is NO, but here in the result of the table it looks like a Yes. As it shuffled the Yes from Monday to Tuesday, from Tuesday to Wed...etc

Then you write this field 'Operates_Text' a couple of times, but where does it come from?

CrossTable(Weekday, Operates_Text, 3)

If I look at the CrossTable sintax, this should be a data field name, but I don't see any data field with this name

crosstable (attribute field name, data field name [ , n ] ) 

[JuMacedo]

@pravinboniface 

I don't think this solution will work as I have to include this on the script, but this is just a part of a whole package of conditions that together will give the availability. The other conditions I have already coded, just need to include this one.