topic Re: Inner Join leads to missing data in App Development

Inner Join leads to missing data

qlikuser22 — Tue, 20 Jul 2021 14:30:34 GMT

I am loading data directly from SQL, I have used appropriate renaming in the load statement when needed. My task is to find the maximum value based on the date and generate the latest data.

So I have fields like month year and day. Using MakeDate function I find the latest date. The issue I face is, there are few employee names that are available in the latest date(example today) but it is not reflecting in the output. If I run the SQL query in the editor, I can find the data, but not when performing inner join in qliksense load script.

I think I am missing something obvious and I am not aware of it. As I am new to this qliksense, any help would be greatly appreciated.

Example:

Table1:

Load Name as [Employee Name], day, month,year ,

MakeDate(year,month,day) as latestdate;

SQL select * from hive.emp.empdata;

Inner Join(Table1)

Load Date(Max(latestdate)) as latestdate

resident Table1;

Note: I am using qliksense September 2020 version

Thanks in advance.

Re: Inner Join leads to missing data

marcus_sommer — Wed, 21 Jul 2021 14:53:23 GMT

In general should your approach work. Possible errors may occur if there is a previous table with the same data-structure like the Table1 and/or any qualifying is used and/or makedate() returned not for all records a valid date because of the data-quality of the source-fields and/or your noticed values doesn't belong to the max-date.

A relative easy check would be if your replaced the inner with a left statement and adds another simple field within the join-load - something like 1 as Flag. Now no reduction will happens and you could identify which of the latestdate is also the max-date.

Update: I think your issue is the missing employee within the aggregation join-load because without all records will be matched against a single-date ...

- Marcus

Re: Inner Join leads to missing data

qlikuser22 — Fri, 23 Jul 2021 09:07:25 GMT

Thanks for the update. I tried with the left join as well, but I can see that I have more records which is not max date as well. SO any other idea will be really helpful.

Re: Inner Join leads to missing data

marcus_sommer — Fri, 23 Jul 2021 09:27:01 GMT

You need to include the employee or any other valid key within the join.

Re: Inner Join leads to missing data

qlikuser22 — Fri, 23 Jul 2021 09:35:19 GMT

Thanks. Does this work?

Table1:

Load Name as [Employee Name], day, month,year ,

MakeDate(year,month,day) as latestdate;

SQL select * from hive.emp.empdata;

Inner Join(Table1)

Load [Employee Name], Date(Max(latestdate)) as latestdate

resident Table1;