Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
mazacini
Creator III
Creator III

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

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

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

6 Replies
Not applicable

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
Creator III
Creator III
Author

That works great!

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

Thank you

Not applicable

in fact there are no '-'

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

qvbitech
Partner - Contributor II
Partner - Contributor II

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

mazacini
Creator III
Creator III
Author

I understand.

Many thanks

mazacini
Creator III
Creator III
Author

Hi

Can you explain?

Thanks

joe