Intervalmatch or other solution

[schurter]

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

[lironbaram]

HI

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

[adamdavi3s]

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

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

[schurter]

Thanks very much for the answer!

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

[schurter]

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...

[Peter_Cammaert]

• 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

[schurter]

I received a good solutions under the German users:

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

Best, Marlies

[Peter_Cammaert]

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.

[schurter]

Dear Peter

Oh, ok, thanks for the Input!

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

Best, Marlies