4 Replies Latest reply: Dec 28, 2014 12:26 AM by alvinford ford

# Most Occurred

Hi All,

Facing an issue to get the below logic. Request your kind help in achieving it.

I have the following data.

 Dress   Type Weight Price Type Price ID Jeans 50 KG Wholesale Price 100 ID1 Jeans 50 KG Wholesale Price 200 ID2 Jeans 50 KG Wholesale Price 100 ID3 Shirts 50 KG Wholesale Price 100 ID1 Shirts 50 KG Wholesale Price 200 ID2 Trousers 100 Pound Street Price 300 ID1 Trousers 100 Pound Street Price 300 ID4 Trousers 100 Pound Street Price 200 ID1 Trousers 100 Pound Street Price 400 ID5 Trousers 100 Pound Street Price 400 ID5 Coat 50 KG Channel Price 200 ID2 Coat 50 KG Channel Price 200 ID1

Based on the column Dress Type, Weight , Price Type and Price . I have to fetch the most occurred price and then count the ID for most occurrences.

Example :

For the Dress Type - Jeans the most occurred price is 100 so fetch the record and count the ID with Price 100 for Jeans.

For Shirts the Price is not repeated , so fetch two records for Shirts.

For Trousers most repeated price is 300 and 400 so fetch both the records and count the ID

For Coat ,as the Price is same  for the records fetch the record and count the ID.

Final output will be as below:

 Dress   Type Weight Price Type Price Count(ID) Jeans 50 KG Wholesale Price 100 2 Shirts 50 KG Wholesale Price 100 1 Shirts 50 KG Wholesale Price 200 1 Trousers 100 Pound Street Price 300 2 Trousers 100 Pound Street Price 400 2 Coat 50 KG Channel Price 200 2

Regards,

Alvin.

• ###### Re: Most Occurred

What you are trying to achieve is a very simple task. Use the rank function available in QlikView.

Instructions:

Create a Straight Table Chart, and add [Dress Type],  Weight, [Price Type] , Price as Dimensions , and then add just one Expression as follows (definition): IF (RANK (COUNT(ID),1,1) = 1, COUNT(ID))

The Engine will aggregate the values for you based on the dimensions up to Price. and the if is just to show all the 1 rank values.

Sometimes We think as programmers and then immediately go for the Code :-) and QlikView has plenty of functions, which We are all discovering on a daily basis.

• ###### Re: Most Occurred

Hi Mario,

Thank you for your reply. It helped. There was a slight change in the requirement for one of the scenario in the data provided.

Current Output is :

 Dress   Type Weight Price Type Price Count(ID) Jeans 50 KG Wholesale Price 100 2 Shirts 50 KG Wholesale Price 100 1 Shirts 50 KG Wholesale Price 200 1 Trousers 100 Pound Street Price 300 2 Trousers 100 Pound Street Price 400 2 Coat 50 KG Channel Price 200 2

Expected Output is :

The Only change in the logic is for Trousers as Dress Type, Weight , Price Type is same the Price has to be displayed as shown below. (for example-300-400)

 Dress   Type Weight Price Type Price Count(ID) Jeans 50 KG Wholesale Price 100 2 Shirts 50 KG Wholesale Price 100 1 Shirts 50 KG Wholesale Price 200 1 Trousers 100 Pound Street Price 300-400 1 Trousers 100 Pound Street Price 301-400 1 Coat 50 KG Channel Price 200 2

Request help to achieve the above logic.

Regards,

Alvin.

• ###### Re: Most Occurred

Hi

Copy paste below code in script editor:

Table1:
[
DressType,Weight,PriceType,Price,ID
Jeans,50 KG,Wholesale Price,100,ID1
Jeans,50 KG,Wholesale Price,200,ID2
Jeans,50 KG,Wholesale Price,100,ID3
Shirts,50 KG,Wholesale Price,100,ID1
Shirts,50 KG,Wholesale Price,200,ID2
Trousers,100 Pound,Street Price,300,ID1
Trousers,100 Pound,Street Price,300,ID4
Trousers,100 Pound,Street Price,200,ID1
Trousers,100 Pound,Street Price,400,ID5
Trousers,100 Pound,Street Price,400,ID5
Coat,50 KG,Channel Price,200,ID2
Coat,50 KG,Channel Price,200,ID1
]
;

count(ID)as CountID
Resident Table1
Group by DressType,Weight,PriceType,Price;

add all fields (including CountID field) and expression:

Aggr(Count(ID),DressType,Weight,PriceType,Price)

in straight table:

Hope either solution CountID field or expression:

Aggr(Count(ID),DressType,Weight,PriceType,Price)

Regards

Neetha

• ###### Re: Most Occurred

Hi Neetha,

I don't want only to count the ID. Based on the most occurred values the display of the values is also different.

Kindly refer to my discussion.