Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Add more fields to load statement!!!

Hello all,

I am trying to add more fields to a load statement with 2 max functions already embedded. I have the field [KE Task ID] which is the key however I want to bring in more fields from the same QVD called, [RC Version Date], [RC Project Name], and [RC Project Number].

However whenever I add these fields directly to the load statement it interferes with the max functions and I get errors, as soon as I remove the fields from the load statement, it runs flawlessly.

Here is the code that runs flawlessly but doesn't have all the fields I need.

LoadJoin:

LOAD *

FROM [\\ke_root.qvd] (qvd);

Left Join

LOAD *

FROM [\\ke_task.qvd] (qvd);

Left Join (LoadJoin)

LOAD [KE Task ID],

    Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

    Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID];


Here is the code that is preferred but does not run without errors...

LoadJoin:

LOAD *

FROM [\\ke_root.qvd] (qvd);

Left Join

LOAD *

FROM [\\ke_task.qvd] (qvd);

Left Join (LoadJoin)

LOAD [KE Task ID],

      [RC Version Date],

      [RC Project Name],

      [RC Project Number],

     Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

     Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID];

Thanks,

Nick

1 Solution

Accepted Solutions
sunny_talwar

May be this:

LoadJoin:

LOAD *

FROM [\\ke_root.qvd] (qvd);

Left Join

LOAD *

FROM [\\ke_task.qvd] (qvd);

Left Join (LoadJoin)

LOAD [KE Task ID],

      [RC Version Date],

      [RC Project Name],

      [RC Project Number],

    Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

    Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];

View solution in original post

5 Replies
sunny_talwar

May be this:

LoadJoin:

LOAD *

FROM [\\ke_root.qvd] (qvd);

Left Join

LOAD *

FROM [\\ke_task.qvd] (qvd);

Left Join (LoadJoin)

LOAD [KE Task ID],

      [RC Version Date],

      [RC Project Name],

      [RC Project Number],

    Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5],

    Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];

Not applicable
Author

Hi Sunny,

I have tried your method and it worked well for me, but I ended up getting 3 duplicates.

I was supposed to have 1992 records, after employing your method I got back 1995 records. How can I fix this?

sunny_talwar

I am not sure what might be the reason for this duplication. But you can try this approach:

LoadJoin:

LOAD *

FROM [\\ke_root.qvd] (qvd);

Left Join

LOAD *

FROM [\\ke_task.qvd] (qvd);

MappingTable1:

Mapping

LOAD [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number] as Key1,

          [Last KE Task Work Info Date Q5];

LOAD [KE Task ID],

      [RC Version Date],

      [RC Project Name],

      [RC Project Number],

    Max(Date([Last KE Task Work Info Date Q5])) as [Last KE Task Work Info Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];


MappingTable2:

Mapping

LOAD [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number] as Key2,

          [Last KE Task Work Info Mod Date Q5];

LOAD [KE Task ID],

      [RC Version Date],

      [RC Project Name],

      [RC Project Number],

    Max(Date([Last KE Task Work Info Mod Date Q5])) as [Last KE Task Work Info Mod Date Q5]

FROM

[\\ke_task_work_info.qvd] (qvd)

Group By [KE Task ID], [RC Version Date], [RC Project Name], [RC Project Number];

And then may be this:

FinalLoad:

LOAD *,

          ApplyMap('MappingTable1', [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number]) as  [Last KE Task Work Info Date Q5],

          ApplyMap('MappingTable2', [KE Task ID]&'|'&[RC Version Date]&'|'&[RC Project Name]&'|'&[RC Project Number]) as  [Last KE Task Work Info Mod Date Q5]

Resident LoadJoin;

DROP Table LoadJoin;

The approach is outlined here:Don't join - use Applymap instead

Not applicable
Author

The original data has duplicates, the max function removes duplicates because it only takes one record that has the highest date per [Known Error ID] key.

For some reason the Max Function isn't working when adding the additional fields and group by in the original answer marked: "Correct Answer"

sunny_talwar

When joining back to the original table (left join or join), you won't reduce the number of rows. Right join can do, but the way it is setup right now, even right join won't work. Do you have a sample you can share of how the data looks and how you want it to end up like?

Best,

Sunny