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]

The screenshot you've shared doesn't look correct. Did you add more tabs to the inline load?

Please repalce the first load statement with this:

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
];

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

The Operates_Text field is replaced with the Operates field in lines 18-24 and then dropped in line 25.

[JuMacedo]

Hi!

Yes I did add tabs, that's why it was so weird. Now I understand.

Thanks, I'll try it and let you know.

[JuMacedo]

It did work, although I had to change part of it. The part that didn't work was the Lef Join, when I tried do this loading data from a resident table but a simple if condition did the job.

Thanks