Inner join with maximum values from two tables

[Anonymous]

Hi all,

I'm trying to join two resident tables taking the maximum values of two tables.  For example if I were to load example tables TableA and TableB below then I'd want to get TableC by taking the maximum value for each category and Id from each table

I've tried a concatenation and a left join (though inner join would be preferable) but I can't seem to get either to work.

Can anyone help please?  Any responses much appreciated.

TableA:

LOAD * INLINE [

    Id, CategoryA, CategoryB, CategoryC, CategoryD

    1, 5, 3, 3, 4

    2, 6, 7, 9, 5

    3, 4, 1, 7, 4

    4, 2, 7, 1, 8

];

TableB:

LOAD * INLINE [

    Id, CategoryA, CategoryB, CategoryC, CategoryD

    1, 3, 3, 6, 5

    2, 1, 1, 4, 4

    3, 6, 7, 6, 6

    4, 5, 7, 6, 3

];

This is what I'd like to achieve:

TableC

    Id, CategoryA, CategoryB, CategoryC, CategoryD

    1, 5, 3, 6, 5

    2, 6, 7, 9, 5

    3, 6, 7, 7, 6

    4, 5, 7, 6, 8

Thank you!

[rubenmarin]

Hi sarah, this script returns that table:

TableA:

LOAD * INLINE [

    Id, CategoryA, CategoryB, CategoryC, CategoryD

    1, 5, 3, 3, 4

    2, 6, 7, 9, 5

    3, 4, 1, 7, 4

    4, 2, 7, 1, 8

];

//TableB:

Concatenate (TableA)

LOAD * INLINE [

    Id, CategoryA, CategoryB, CategoryC, CategoryD

    1, 3, 3, 6, 5

    2, 1, 1, 4, 4

    3, 6, 7, 6, 6

    4, 5, 7, 6, 3

];

TableC_tmp:

NoConcatenate

LOAD Id,

    Max(CategoryA) as CategoryA,

    Max(CategoryB) as CategoryB,

    Max(CategoryC) as CategoryC,

    Max(CategoryD) as CategoryD

Resident TableA Group By Id;

DROP Table TableA;

[Kushal_Chawda · Accepted solution]

another way of doing if field name in both the tables are not same

TableA:

LOAD * INLINE [

    Id, CategoryA, CategoryB, CategoryC, CategoryD

    1, 5, 3, 3, 4

    2, 6, 7, 9, 5

    3, 4, 1, 7, 4

    4, 2, 7, 1, 8

];

Left Join(TableA)

LOAD * INLINE [

    Id, CategoryA_1, CategoryB_1, CategoryC_1, CategoryD_1

    1, 3, 3, 6, 5

    2, 1, 1, 4, 4

    3, 6, 7, 6, 6

    4, 5, 7, 6, 3

];

Final:

LOAD Id,

RangeMax(CategoryA,CategoryA_1) as CategoryA,

RangeMax(CategoryB,CategoryB_1) as CategoryB,

RangeMax(CategoryC,CategoryC_1) as CategoryC,

RangeMax(CategoryD,CategoryD_1) as CategoryD

Resident TableA;

DROP Table TableA;

[jyothish8807]

Hi Sarah,

There is no need to join the table sin the script level.

In front end in a straight chart:

In dimension:

ID

In expression:

1. Max(CatgA)

2.MAx(CatgB)

and so on.

Hope it helps.

Regards

KC

[Anonymous]

Thank you Kush, that's perfect.  My field headings won't be the same and this gives me exactly what I needed.

[Anonymous]

Hi Jyothish,

Thanks for getting back to me, but I'm afraid I have to do this within a script as this is part of the transform part and data will be saved in QVDs rather than displayed in charts.

Thanks,

Sarah

[Anonymous]

Thank you Ruben for responding so quickly.  Couldn't quite get this method to work though as there is a problem with the field names, I tried to fix it with qualify/unqualify but it wouldn't work.  It's sorted now anyway.