Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How can I validate a date inside a Load?

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

t1.PNG

Table: Services

t2.PNG

Thanks in advance.

1 Solution

Accepted Solutions
OmarBenSalem

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;

Capture.PNG

View solution in original post

8 Replies
OmarBenSalem

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;

Capture.PNG

Anonymous
Not applicable
Author

Thanks for answering.

Why do you use a Date#() inside of a date() ?

OmarBenSalem

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?

Anonymous
Not applicable
Author

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;

OmarBenSalem

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;

Anonymous
Not applicable
Author

Perfect, thank you!

Anonymous
Not applicable
Author

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.

OmarBenSalem

You can always find some discussions treating this.

For example:

Join tables after using intervalmatch