Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So, I am trying to figure out how to do an auxiliary table showing the ServiceID and the name of the Representative based on the region and the time frame that each representative worked.
I've tried the following:
Relation:
Load
ServiceID,
Date,
Region
Resident Services;
Join
Load
Region,
Representative
Resident RepHist
Where
Start<Date and
not(End<Date);
Here is an example of my structure:
Table: RepHist
Table: Services
Thanks in advance.
services:
LOAD ServiceID, date(Date#(DateField,'DD/MM/YYYY')) as DateField, Region Inline [
ServiceID, DateField, Region
4487, 21/10/15, America
4488, 18/02/2016, Europe
4489, 26/04/2016, Europe
4490, 30/03/2017, America
4491, 10/06/2017, America
];
repHist:
LOAD Representative, Region, date(Date#(Start,'DD/MM/YYYY')) as Start,
if(len(trim( date(Date#(End,'DD/MM/YYYY'))))=0,date(Today()),date(Date#(End,'DD/MM/YYYY'))) as End INLINE [
Representative, Region, Start, End
Jhon, America, 01/01/2015,30/12/2016
David, America, 01/01/2017,
Alan, Europe, 01/01/2016,
];
Inner Join IntervalMatch ( DateField )
LOAD Start, End
Resident repHist;
services:
LOAD ServiceID, date(Date#(DateField,'DD/MM/YYYY')) as DateField, Region Inline [
ServiceID, DateField, Region
4487, 21/10/15, America
4488, 18/02/2016, Europe
4489, 26/04/2016, Europe
4490, 30/03/2017, America
4491, 10/06/2017, America
];
repHist:
LOAD Representative, Region, date(Date#(Start,'DD/MM/YYYY')) as Start,
if(len(trim( date(Date#(End,'DD/MM/YYYY'))))=0,date(Today()),date(Date#(End,'DD/MM/YYYY'))) as End INLINE [
Representative, Region, Start, End
Jhon, America, 01/01/2015,30/12/2016
David, America, 01/01/2017,
Alan, Europe, 01/01/2016,
];
Inner Join IntervalMatch ( DateField )
LOAD Start, End
Resident repHist;
Thanks for answering.
Why do you use a Date#() inside of a date() ?
I'm using both; since I'm defining the date field directly into an inline table; qlik is logically not recognizing the as date but as string..
and so, with date# I'm sort of casting the field; telling qlik to treat the field as if it was a date with a certain format; then with the date() function, I really transform each value to a real date !
But, for ur case, u're not obligated to do that if Qlik is already recognizing ur date field as date !
So, did it work for u?
I am trying to adapt it to my load script, but I had no success:
SpecialTemp:
Load
ServiceID as Special.SaleID,
OrderDate as Special.OrderDate,
Region as Special.Region
Resident [Services];
Load
Region as Special.Region,
Representative as Special.Representative,
StartDate as Special.StartDate,
if(
len(trim(date(EndDate)))=0,
date('30/06/2009'),
date(EndDate)
) as Special.EndDate
Resident [RepHist];
Inner Join IntervalMatch ( Special.OrderDate )
Load
Special.StartDate,
Special.EndDate
Resident SpecialTemp;
try:
SpecialTemp:
Load
ServiceID as Special.SaleID,
OrderDate as Special.OrderDate,
Region as Special.Region
Resident [Services];
specialTemp2:
Load
Region as Special.Region,
Representative as Special.Representative,
StartDate as Special.StartDate,
if(
len(trim(date(EndDate)))=0,
date('30/06/2009'),
date(EndDate)
) as Special.EndDate
Resident [RepHist];
Inner Join IntervalMatch ( Special.OrderDate )
Load
Special.StartDate,
Special.EndDate
Resident specialTemp2;
Perfect, thank you!
Is there a way to unite all this content in a single table? I know the question seems dumb, but I can't add more columns to the Inner Join IntervalMatch and if I add a Join after the Inner Join it creates a cartesian product.
You can always find some discussions treating this.
For example: