Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mazacini
Contributor 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

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

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

6 Replies
Not applicable

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

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
Contributor III

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

That works great!

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

Thank you

Not applicable

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

in fact there are no '-'

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

qvbitech
New Contributor II

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

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

mazacini
Contributor III

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

I understand.

Many thanks

mazacini
Contributor III

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

Hi

Can you explain?

Thanks

joe

Community Browser