Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
jamielim
Contributor III
Contributor III

Table A Left Join Table B error

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. !

1 Solution

Accepted Solutions
Anonymous
Not applicable

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.

Capture.JPG

View solution in original post

10 Replies
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
Contributor III
Contributor III
Author

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
Not applicable

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
Contributor III
Contributor III
Author

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
Master III
Master III

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
Not applicable

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.

Capture.JPG

jamielim
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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