Table A Left Join Table B error

[jamielim]

I am trying to connect 2 tables - a Calendar and a PH table containing the Public Holiday Dates and Public Holiday Names.

The key that connects the 2 tables is BusinessDate. I used Left Join (MasterCalendar) as I want to retain all dates in the Master Calendar and be able to identify the Public Holiday name if it is a public holiday.

I am loading the distinct records of Public Holiday dates as the originating tables had duplicates in them.

When I load the 2 tables individually, they look perfectly fine. However, when I join them, the Public Holiday name is not coming through.

Can anyone help to see what is wrong with this data load?

MasterCalendar:

Load

  Date(TempDate,'DD/MM/YYYY') as BusinessDate

Resident TempCalendar;

Drop Table TempCalendar;

PH:

LOAD distinct

    Date(PHDate,'DD/MM/YYYY') as BusinessDate,

    capitalize (PHName) as PH_Name

FROM [lib://QLIKQVD (qliksense_administrator)/eHR_PH.qvd] (qvd);

left join (MasterCalendar)

Load * resident PH;

Drop table PH;

Thanks. !

[its_anandrjs]

Hi,

Be insure from your end to check both key fields in the table has proper date format

Key Fields

Date(TempDate,'DD/MM/YYYY') as BusinessDate

Date(PHDate,'DD/MM/YYYY') as BusinessDate

Or you can try with Numeric key fields of the date field

Ex:-

MasterCalendar:

Load

  Date(TempDate,'DD/MM/YYYY') as BusinessDate,

  NUM(Date(TempDate,'DD/MM/YYYY')) as NumBusinessDate

Resident TempCalendar;

Drop Table TempCalendar;

left join (MasterCalendar)

LOAD distinct

    Date(PHDate,'DD/MM/YYYY') as PHDate,

    NUM(Date(PHDate,'DD/MM/YYYY')) as NumBusinessDate,

    capitalize (PHName) as PH_Name

FROM [lib://QLIKQVD (qliksense_administrator)/eHR_PH.qvd] (qvd);

Regards

Anand

[jamielim]

Hi Anand,

I've checked the format of the dates and they are correct.

I also tried your suggestion of converting them to NUM and still got the same results.

Regards,

Jamie

[Anonymous]

Hi Jamie,

Can you attach your file?

I've tested your 'data load' script - and it's working on my side.

After joining - you cannot see PH field or you can see the field, but there is no PH' names?

[its_anandrjs]

Hi,

Provide sample data or you can check your qvd location will it perfect or not.

FROM [lib://QLIKQVD (qliksense_administrator)/eHR_PH.qvd] (qvd);

Regards

Anand

[jamielim]

Hi Grzegorz,

My file is too big to be attached.

I can see the PH-Name field, but there are no entries coming up against it. Seems to be Null, even though the Business Dates that are also PH are showing up.

Regards,

Jamie

[antoniotiman]

Hi Jamie,

try this

PH:

LOAD distinct  (remove distinct)

left join (MasterCalendar)

Load * resident PH;

change to

left join (MasterCalendar)

Load BusinessDate,MaxString(PH_Name) as PH_Name

resident PH

Group By BusinessDate;

[Anonymous · Accepted solution]

There is my test file (as you can see there are some PH names)

Try it and let me know if you experience the same issue.

[jamielim]

Hi Grzegorz,

I tested your qvf and it worked. I will go back to review my data again before updating all. Thanks.

Regards,

Jamie

[jamielim]

Hi Antonio, Thanks, but it didn't solve the issue. I will review my data again just to ensure I have not missed out something. Will update all on this. Thanks.

Regards,

Jamie