Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

alvinford
Not applicable

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   TypeWeightPrice TypePriceID
Jeans50 KGWholesale Price100ID1
Jeans50 KGWholesale Price200ID2
Jeans50 KGWholesale Price100ID3
Shirts50 KGWholesale Price100ID1
Shirts50 KGWholesale Price200ID2
Trousers100 PoundStreet Price300ID1
Trousers100 PoundStreet Price300ID4
Trousers100 PoundStreet Price200ID1
Trousers100 PoundStreet Price400ID5
Trousers100 PoundStreet Price400ID5
Coat50 KGChannel Price200ID2
Coat50 KGChannel Price200ID1

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   TypeWeightPrice TypePriceCount(ID)
Jeans50 KGWholesale Price1002
Shirts50 KGWholesale Price1001
Shirts50 KGWholesale Price2001
Trousers100 PoundStreet Price3002
Trousers100 PoundStreet Price4002
Coat50 KGChannel Price2002

Very thanks in advance.

Regards,

Alvin.

1 Solution

Accepted Solutions
Not applicable

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.

4 Replies
Not applicable

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.

neetha_p
Not applicable

Re: Most Occurred

Hi

Copy paste below code in script editor:

Table1:
LOAD * Inline
[
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
]
;

LOAD DressType,Weight,PriceType,Price,
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:

Inline1.png

Hope either solution CountID field or expression:

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

hope it solves your query

Regards

Neetha

alvinford
Not applicable

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   TypeWeightPrice TypePriceCount(ID)
Jeans50 KGWholesale Price1002
Shirts50 KGWholesale Price1001
Shirts50 KGWholesale Price2001
Trousers100 PoundStreet Price3002
Trousers100 PoundStreet Price4002
Coat50 KGChannel Price2002

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   TypeWeightPrice TypePriceCount(ID)
Jeans50 KGWholesale Price1002
Shirts50 KGWholesale Price1001
Shirts50 KGWholesale Price2001
Trousers100 PoundStreet Price        300-4001
Trousers100 PoundStreet Price        301-4001
Coat50 KGChannel Price2002

Request help to achieve the above logic.

Regards,

Alvin.

alvinford
Not applicable

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.

Thank you for your reply...

Regards,

Alvin.