Loading Excel Sheets

[Anonymous]

Good morning friends!

I'm having a hard time creating a group of tables in QlikView from a database in an excel spreadsheet, the situation is as follows; I have a spreadsheet with operational schema of transport of orders of a certain company, it has two tabs, one with information of IDA and in the other information of VOLTA, in these tabs it contains information of frequencies of schedules like; Route, Origin, Destination, Time of departure, Sections by which the time passes, UF, Days of the week information of the type of the operation and the table with the arrival times, route between one location to another, time to stop in the certain place and Time of departure, according to the day of the coming week. That's fine. I happened to have read the worksheet so that QlikView could read, but it happens that it can read neatly one of the information either IDA or BACK of the trip;

Example;

Cuiabá x Santarem 05:00 Hrs, Pass through the cities A, B, C, D, E, F, G, H in the IDA,

Santarem x Cuiabá 13:30 Hrs, Pass through the cities G, D, C, B, A in the Round

Each city has its arrival time, exit time and time to be stopped, but when I put this scheme in QlikView or IDA or VOLTA they are sorted correctly with their respective schedules and mileage according to who I first load in the Script, but when Mute the direction of the trip it becomes disorganized, as the tables are classified as original load order, and all cities are in a single line called LOCALITIES in the worksheet, when the direction is changed and the return has fewer cities to The time passes, it gets messed up.

Right

Santarem x Cuiabá 13:30 Hrs, Pass through the cities G, D, C, B, A in the Round

How QlikView loads

Santarem x Cuiabá 13:30 Hrs, Pass through the cities A, B, G, D, C in the Round

Material is attached, please kindly, someone can help me in this situation.

Sincerely

Johnathan Conrado

[petter]

Relying on load order sorting will never really work well. Since this is routes and departures, arrivals, stops etc. everything should be sorted according to date and time. You have all this information. You will have to make full date and times out of every significant event and you could also operate in intervals with the use of Interval#() and Interval() functions for the appropriate events.

[Anonymous]

Dear Petter Skjolden

Thank you very much for the feedback, I could not understand the solution, I realized that one of my errors may be in order of loading, but I did not understand how to correct this, it would be possible for you to create this event on a smaller scale as an example in the documents that I sent

Best regards

[petter · Accepted solution]

I add a sequence# or stop# for each LOCALIDADE named LOC# which can be used as the sort field to get the stops in the right order independent of the direction of a route.

I didn't do anything with intervals or dates.But I had to throw in super-simple mapping ...

[Anonymous]

Good friend,

I took the file you attached and copied the script and pasted it to my QlikView file, I do not know if I would have to make any other changes, because it still keeps loading in a mess, you can configure by selecting in the tab of the main panel Cuiaba x Santarém route, And tab "Tabela de Horários", will load both; Round trips, when I select the trip, everything is ok, the locations with their exact times and km appear, more when I select the return in the case, origin santarém x cuiabá, continued in disorder, I hope you can still help me.

Grateful

[petter]

I didn't notice that the LOCALIDADE was non-unique - so I fixed that...

[petter]

and .... I removed some empty rows that should come into QlikView ....

[Anonymous]

Good morning mate!

I realized that the problem with the first project in the first project that you sent "Esquema Operacional2" could be corrected in the worksheet, the problem was occurring on the routes that contained repeated information, such as in the route I mentioned (cuiaba x santarém) The same locations with the same mileage but not the same time, when you exclude in the worksheet that repetition of locations QlikView read without major problems.

Thank you very much