topic Re: Intervalmatch or other solution in QlikView

Intervalmatch or other solution

schurter — Mon, 13 Mar 2017 06:15:38 GMT

Good morning

I would work with the following 2 tables:

- Kapazitäten (capacities)-> the offered time per date, time and place

- IST-Daten -> the live data

The table-structur is as follows:

In the table IST-Daten I would calculate how well the capacities ar used. Therefore I would add two new column (from Table Kapazitäten: KapazitätenDatumStartzeit and KapazitätenDatumStopzeit) in this table.

I tried with Intervalmatch but unfortunately without solution for two columns.

Attached you see the qlikview-file.

Thank you very much for your help!

Marlies

Re: Intervalmatch or other solution

lironbaram — Mon, 13 Mar 2017 07:33:17 GMT

your problem is you have to time ranges in the two tables

when you use intervalmatch in one table you should have only one time field

for example you can create one timestamp for the activity any 5 minutes and then connect the two tables

Re: Intervalmatch or other solution

adamdavi3s — Mon, 13 Mar 2017 08:00:08 GMT

Can you not do this with a left join and an if

if(Ist_Naht >= KapazitätDatumStartzeit AND Ist_Schnitt <= KapazitätDatumStopzeit,1,0)

Re: Intervalmatch or other solution

schurter — Mon, 13 Mar 2017 16:41:28 GMT

Thanks very much for the answer!

Perhaps any 15 Minutes should be enough. How can I make this?

Re: Intervalmatch or other solution

schurter — Mon, 13 Mar 2017 16:46:25 GMT

I don't understand your answer.

How can I make a left join when I've not the same key. In the table IST-Daten are timestamps like 07:41 and in the capacities are timestamps like 07:40...

Re: Intervalmatch or other solution

Peter_Cammaert — Mon, 13 Mar 2017 16:54:43 GMT

Lift Min and Max timestamps from your IST-Daten.
Create a table with just DateTime values every 15 minutes between Min and Max timestamps.
Use INTERVALMATCH to match all these 15-min values to the OP_KISIM.Ist_Schnitt -> OP_KISIM.Ist_Naht periods and join them into IST-Daten. You will get a huge list of 15-minute DateTime rows.
Use you initial INTERVALMATCH to map the Kapazitäten table onto this huge list.

The first INTERVALMATCH can be replaced by a LOAD ... FROM ... WHILE loop to duplicate rows in IST-Daten as long as you have 15-min pieces. Slower but uses actual start & stop timestamps.

Best,

Peter

Re: Intervalmatch or other solution

schurter — Mon, 27 Mar 2017 12:18:11 GMT

I received a good solutions under the German users:

https://community.qlik.com/message/1238110#1238110

Best, Marlies

Re: Intervalmatch or other solution

Peter_Cammaert — Mon, 27 Mar 2017 12:48:37 GMT

Please note that the link you mentioned refers to a post in a private group. Most of us do not have access to that group.

It would be helpful if you could summarize the solution in this thread as well. Thanks.

Re: Intervalmatch or other solution

schurter — Mon, 27 Mar 2017 13:11:05 GMT

Dear Peter

Oh, ok, thanks for the Input!

Please see attachement. I received it without comment, but it is very helpful.

Best, Marlies