Inner Join leads to missing data

[qlikuser22]

Hi

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. 

[marcus_sommer]

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

[qlikuser22]

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. 

[marcus_sommer]

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

[qlikuser22]

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;