Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

3ernardo
New Contributor III

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
Esteemed Contributor

Re: How can I validate a date inside a Load?

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

8 Replies
OmarBenSalem
Esteemed Contributor

Re: How can I validate a date inside a Load?

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

3ernardo
New Contributor III

Re: How can I validate a date inside a Load?

Thanks for answering.

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

OmarBenSalem
Esteemed Contributor

Re: How can I validate a date inside a Load?

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?

3ernardo
New Contributor III

Re: How can I validate a date inside a Load?

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
Esteemed Contributor

Re: How can I validate a date inside a Load?

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;

3ernardo
New Contributor III

Re: How can I validate a date inside a Load?

Perfect, thank you!

3ernardo
New Contributor III

Re: How can I validate a date inside a Load?

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
Esteemed Contributor

Re: How can I validate a date inside a Load?

You can always find some discussions treating this.

For example:

Join tables after using intervalmatch

Community Browser