Add more fields to load statement!!!

[#1]

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

[sunny_talwar · Accepted solution]

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];

[#3]

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

[#5]

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