Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Mateo31
New Contributor

Added new column with Max Value and Concatenation

Hi everyone,

I have the following table:

Product_ID

Rate

Category

LENUSF5

3

B

LENUSF5

2

B

LENUSF6

4

C

LENUSF7

3

B

LENUSF8

3

B

LENUSF9

3

B

LENUSF10

3

B

LENUSF11

1

A

LENUSF12

6

D

LENUSF13

10

E

LENUSF14

1

A

LENUSF15

1

A

LENUSF16

1

A

LENUSF16

2

A

 

I need to create a new field with the Combination of the columns Rate and Category by Product_ID(example: 3 and B become 3B). 

But If there are more than one Rate per Product_ID, I just need to use the larger Rate value (example: use 3B instead of 2B for LENUSF5)

Desire result

Product_ID

Rate

Category

Combined_field

LENUSF5

3

B

3B

LENUSF5

2

B

Don’t show

LENUSF6

4

C

4C

LENUSF7

3

B

3B

LENUSF8

3

B

3B

LENUSF9

3

B

3B

LENUSF10

3

B

3B

LENUSF11

1

A

1A

LENUSF12

6

D

6D

LENUSF13

10

E

E10

LENUSF14

1

A

1A

LENUSF15

1

A

1A

LENUSF16

1

A

Don’t show

LENUSF16

2

A

2A

 

Thanks in advance.

Mateo.

7 Replies
Highlighted
Partner
Partner

Re: Added new column with Max Value and Concatenation

Try this at script level:

Raw:
LOAD * INLINE [
Product_ID,Rate,Category
LENUSF5,3,B
LENUSF5,2,B
LENUSF6,4,C
LENUSF7,3,B
LENUSF8,3,B
LENUSF9,3,B
LENUSF10,3,B
LENUSF11,1,A
LENUSF12,6,D
LENUSF13,10,E
LENUSF14,1,A
LENUSF15,1,A
LENUSF16,1,A
LENUSF16,2,A
];
NoConcatenate
tempData:
load Product_ID,max(Rate)as Rate,Category
resident Raw
group by Product_ID,Category
;
drop table Raw;

Data:
load *,Rate&Category as [Combined_Field]
resident tempData;
drop table tempData;

Highlighted
Luminary
Luminary

Re: Added new column with Max Value and Concatenation

I'm assuming that the Category is always the same for the Product_ID. So, this statement should work.

YourTable:
Load
   Product_ID,
   Category,
   Max(Rate) as Rate
Resident YourSourceTable     // or From wherever you have the data.
Group By
   Product_ID,
   Category   

Let me know if that works.

Highlighted
Mateo31
New Contributor

Re: Added new column with Max Value and Concatenation

Thanks for your help.

With your solution I was able to find the Max Rate. But, I also need to create a new field with the combination of Max rate and Category.

New_Field: 10E

Where 10 is (Max_Rate) and E (Category).

Thank you.

Highlighted
Partner
Partner

Re: Added new column with Max Value and Concatenation

Hi,

try with something like this

TABLE:

LOAD * FROM....;

Left Join
LOAD
Product_ID,
Max(Rate) as Rate,
1 as Combined_field_temp
Resident TABLE
Group By Product_ID
;

NoConcatenate
OUTPUT:
LOAD
Product_ID,
Rate,
Category,
If(Combined_field_temp=1,Rate & Category) as Combined_field
Resident TABLE;

DROP TABLE TABLE;

Highlighted
Partner
Partner

Re: Added new column with Max Value and Concatenation

The field has being created named Combined Field in the script provided. 

You just need to call that field out in the table.

Highlighted
Luminary
Luminary

Re: Added new column with Max Value and Concatenation

Oops... forgot to add that... This is all yo need.

YourTable:
Load
   *,
   Rate & Category as RateCategory;
Load
   Product_ID,
   Category,
   Max(Rate) as Rate
Resident YourSourceTable     // or From wherever you have the data.
Group By
   Product_ID,
   Category   

Highlighted
Digital Support
Digital Support

Re: Added new column with Max Value and Concatenation

Mateo, it seems you may have the solution at this point, I just wanted to remind you to be sure to use the Accept as Solution button on the post(s) that you needed to get things working.  This gives credit to those that wrote those up and it lets others know what actually worked for you.  If you are still working on things even after the last few new posts, leave another update when you can.

Regards,
Brett

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.