Create flag in load for max value in dim B for each value in dim A

[mazacini]

Hi

I want script to create flag to show the max value in dim B for each value in dim A, as highlighted below:

dim A	dim B	Val	Flag
x0001	1	1000	0
x0001	2	1000	0
x0001	3	1000	0
x0001	4	1000	0
x0001	5	1000	1
x0002	1	750	0
x0002	2	750	0
x0002	3	750	0
x0002	4	750	1
x0003	1	1100	0
x0003	2	1100	1

Rgds

Joe

[#2 · Accepted solution]

be ware of the synthetic keay (if your table got large amount of data)

test:

LOAD * INLINE [

    dimA, dimB, val

    x001, 1, 1000

    x001, 2, 1000

    x001, 3, 1000

    x001, 4, 1000

    x002, 1, 750

    x002, 2, 750

    x002, 3, 750

    x002, 4, 750

    x003, 1, 1100

    x003, 2, 1100

];

test2:

load dimA, max(dimB) as dimB, 'x' as Flag resident test group by dimA;

left join (test) load * resident test2;

drop table test2

[mazacini]

That works great!

Is there a way to replace the '-' values generated in "Flag" with a 0?

Thank you

[#4]

in fact there are no '-'

this sign is added in teh presentation level and means it it emply (null())value

[qvbitech]

Solution is right but it may cause performance problems with large data sets. It's better to make group by's in SQL.

[mazacini]

I understand.

Many thanks

[mazacini]

Hi

Can you explain?

Thanks

joe