LEFT JOIN doesn't match all data

[jasmina_karcic]

Hi experts,

I have some different situation, and it's very annoying me 😄 So I need your help.

I have a table:

I need to make Table:

I will present now how I am making this:

1. step

tmp:

LOAD

Client,

date(min(Date From)) as Min_Date,

date(max(Date To)) as Max_Date

FROM Table GROUP BY Client;

2. step

tmp2:

LOAD 

Client,
Date(MIN_DATE + IterNo() - 1) as DATE
Resident tmp
While IterNo() <= MAX_DATE - MIN_DATUM + 1;

3. step

left join (tmp2)

LOAD

Client,

date(Date From) as DATE,

Manager

from Table;

Now I should get:

But no, with left join I get just first value for manager:

If I use just join I get:

If this was okay, I would fill empty places with peek(), but look how is working.

😕

Any idea?

Thank you

Jasmina

[marcus_sommer]

It's more complicated as needed because you could apply an IntervalMatch - Qlik Community - 1464547 logic whereby it's even more simple with something like:

load Client, Manager, date([Date to]+ iterno() - 1) as Date
from table while [Date to]+ iterno() - 1 <= [Date from];

- Marcus

[jasmina_karcic]

Uhh, I am now reading this. 

Here are 2 tables, Events and Intervals.

I have just one table. Every Client has a few Managers. Every Manager for each his Client has Date From and Date to.

How to get all dates from intervals and create table of Events?

Thanks

[marcus_sommer]

It's just an example how normal tables might be associated with interval-tables - just to demonstrate the logic behind it. In many scenarios it's not needed because you could transform the interval-table into a normal-table - and that it's. All relevant data are there and this table could be connected to a master-calendar and more is usually not needed.

- Marcus 

[jasmina_karcic]

Event:
LOAD
Client
Manager,
date(Date_from) as Date
From Table;

Intervals:

LOAD
Client,
Manager,
Date_from,
Date_to
from Table;

interval_match:
IntervalMatch (Date)
Load distinct Date_from, Date_to resident Intervals;

This doesn't give me anything what I want. I need normal table, where I can see for every Client, on every date which is his Manager.

Why join, which I show up is working wrong?

Calendar, how it can works, because not all clients has the same data intervals...

😕

[marcus_sommer]

I didn't suggest to do it in this way. It looked that you haven't an event-table and loading the interval-table twice with Date_from as date-field is here not suitable. Did you try my example of:

load Client, Manager, date([Date to]+ iterno() - 1) as Date
from table while [Date to]+ iterno() - 1 <= [Date from];

I use such logics quite often and it does what it should. Further be aware that - at least on the example records of your screenshots - the intervals are overlapping, like: from goes to 05.01 and the next line starts with 05.01. If this isn't wanted else a data-quality issue it should be corrected before the interval-transformation is applied.

- Marcus

[jasmina_karcic]

Unfortunately, doesn't help. I tried your suggestion 😞

Even when I put just < (not <=Date_to)  it makes again duplicate.

I cannot understand if I create table with start point and end point 

Table:

Client,

Min_Date,

Max_Date

Then I add field Date (by this query what you are suggesting), and I normal get:

Table:

Client,

Min_Date,

Max_Date,

Date (all dates between Min and Max)

Then I want make join /left join

Client,

Manager,

Date_from as Date

How this doesn't work good? 😕

Not important if I have the same Date_to, like Date_from, because I take just Date_from. 😕

Left join zero points 😄

Join, give me duplicate value for dates..:/

I will not give up from this 😄 I 

Jasmina

[jasmina_karcic]

When I take these date what I give you:

Table1:
LOAD * Inline [

CLIENT , MANAGER , DATE_FROM, DATE_TO
'A', 'X', '1.1.2022', '5.1.2022'
'A', 'Y', '5.1.2022', '12.1.2022'
'A', 'Z', '12.1.2022', '15.1.2022'
];

STORE Table1 INTO 'lib://folder_E_Qlik_Qliksense (bbi_bi.dom)/Table1.qvd'(qvd);
DROP table Table1;

Table2:

Load
CLIENT,
DATE(MIN(DATE_FROM)) AS MIN_DATE,
DATE(MAX(DATE_TO)) AS MAX_DATE
FROM [lib://folder_E_Qlik_Qliksense (bbi_bi.dom)/Table1.qvd]
(qvd) GROUP BY CLIENT;

Table3:
load
CLIENT,
Date(MIN_DATE + IterNo() - 1) as DATE
Resident Table2
While MIN_DATE +iterno() - 1 <MAX_DATE;

LEFT JOIN(Table3)
Load
CLIENT,
MANAGER,
DATE(DATE_FROM) AS DATE
FROM [lib://folder_E_Qlik_Qliksense (bbi_bi.dom)/Table1.qvd]
(qvd) ;

EXIT Script;

This work:

But the same thing have problem with my real data....:/

[marcus_sommer · Accepted solution]

I don't see a real benefit by your approach because you need a lot more efforts and load-steps because of:

1.  aggregating-load to get min/max dates
2.  internal while-loop to populate the intervals into real dates
3.  joining the origin interval-table against the populated-table
4.  not yet applied but needed - filling the missing dates with interrecord-functions

IMO there are lot of potential risks and/or further things which needed be in account to add and/or adjust some logics, like:

- risks of changing the number of records in regard to the used join-mode
- using one of the from/to fields as key for the join
- are overlapping intervals really considered
- probably some more aspects …

I don't want to say that's not possible to get such a logic to work but my suggestion is much more simple. And should overlapping intervals be corrected it might be done here, too, maybe with a preceeding-approach like:

load Client, Manager, date([Date to]+ iterno() - 1) as Date
while [Date to]+ iterno() - 1 <= [Date from];
load
   Client, Manager, [Date from],
   if(Client = previous(Client) and [Date to]  = previous([Date from]),
   date([Date to] + 1), [Date to]) as [Date to]
resident table order by Client, [Date to];

You may need within your real data some adjustments to the order by and/or by checking/adjusting the values with the interrecord-functions especially if you don't want to add 1 to [Date to] else to subtract -1 from [Date from] but in general are these methods working - it's now a logically fine-tuning to get the wanted output.

- Marcus

[jasmina_karcic]

Thank you Marcus, I will try this last what You suggested. 

I've solved my problem. I didn't use before floor() function, and also order in primary table. When I put that, then lef join worked good. 

I filled null values for Manager with peek function.

But I will also try your suggestion. 

Thank you.

Jasmina