Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
That works great!
Is there a way to replace the '-' values generated in "Flag" with a 0?
Thank you
in fact there are no '-'
this sign is added in teh presentation level and means it it emply (null())value
Solution is right but it may cause performance problems with large data sets. It's better to make group by's in SQL.
I understand.
Many thanks
Hi
Can you explain?
Thanks
joe