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

    Most Occurred

    alvinford ford

      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.

        • Re: Most Occurred
          Mario Estrada

          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
              alvinford ford

              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.

            • Re: Most Occurred
              neetha P

              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