User Details Missing

[Saro_2306]

Hi all,

I am Having User Details (includes User name, DOJ, User Code etc.) in one sheet considering this as Main sheet.

User's Production Details (Includes Months, Week, Date, Production Units, Target ) in Another sheet.

User Name is Common Field for both Sheets.  Date field not available in Main Sheet.

In Front End When No Date filter checked, it display all users data and shows 0 for whose production details missing in Sheet 2.  But when I Click Month it shows only user's Details whose Production Details available in Sheet 2, it Hides User's Details whose production details not available in Sheet.

I want to show those users details as 0. Any body can help.

Thanks in Advance. 

[avinashelite]

Please share the sample data so that we could check and provide the solution 

[Saro_2306]

Hi @avinashelite please find example data as you requested.

[avinashelite · Accepted solution]

Actually this is missing data scenario  , you need to replicate/duplicate the data for missing months it will lead to additional data . Check the solution below

Test:
LOAD [Emp Code],
[Employee Name],
[User id],
Team
FROM
[C:\Users\Downloads\Example Data.xlsx]
(ooxml, embedded labels, table is [User Details]);

Data:
LOAD [Full Name],
[Full Name] as [Employee Name],
[Production Hours],
Month
FROM
[C:\Users\Downloads\Example Data.xlsx]
(ooxml, embedded labels, table is [Production Details]);

Test1:
LOAD
Month
Resident
Data;

Join(Test1)
LOAD
[Employee Name] as EMP
Resident
Test
Where Not Exists([Full Name],[Employee Name]);

join(Data)
LOAD
Month,
EMP as [Employee Name]
Resident
Test1;

Drop Table Test1;

[Saro_2306]

Thanks @avinashelite . Its Working. I made few changes for my script.