topic Re: Inner join with maximum values from two tables in QlikView

Inner join with maximum values from two tables

Anonymous — Tue, 10 Nov 2015 10:20:17 GMT

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!

Re: Inner join with maximum values from two tables

rubenmarin — Tue, 10 Nov 2015 10:42:25 GMT

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;

Re: Inner join with maximum values from two tables

Kushal_Chawda — Tue, 10 Nov 2015 10:55:18 GMT

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;

Re: Inner join with maximum values from two tables

jyothish8807 — Tue, 10 Nov 2015 10:56:25 GMT

Hi Sarah,

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

In front end in a straight chart:

In dimension:

In expression:

1. Max(CatgA)

2.MAx(CatgB)

and so on.

Hope it helps.

Regards

Re: Inner join with maximum values from two tables

Anonymous — Tue, 10 Nov 2015 11:06:32 GMT

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

Re: Inner join with maximum values from two tables

Anonymous — Tue, 10 Nov 2015 11:07:37 GMT

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

Re: Inner join with maximum values from two tables

Anonymous — Tue, 10 Nov 2015 11:09:34 GMT

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.