Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
jamielim
Not applicable

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

Re: Table A Left Join Table B error

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

10 Replies
its_anandrjs
Not applicable

Re: Table A Left Join Table B error

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

Re: Table A Left Join Table B error

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

gsmajdor
Not applicable

Re: Table A Left Join Table B error

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

Re: Table A Left Join Table B error

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

Re: Table A Left Join Table B error

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

Re: Table A Left Join Table B error

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;

gsmajdor
Not applicable

Re: Table A Left Join Table B error

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

Re: Table A Left Join Table B error

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

Re: Table A Left Join Table B error

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