5 Replies Latest reply: Feb 19, 2015 2:26 PM by Massimo Grossi RSS

    How to Achieve the logic in the script

    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: How to Achieve the logic in the script
          Chris Deniziak

          Hi Alvin,

           

          Try this out:

           

          Data:

          LOAD * INLINE [

              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

          ];

           

          _temp_Data:

          LOAD

            [Dress Type],

            Weight,

            [Price Type],

            Price,

            count(ID) as Count

          Resident Data

          Group By [Dress Type],

            Weight,

            [Price Type],

            Price;

           

          DROP Table Data;

           

          Left Join(_temp_Data)

          LOAD

            [Dress Type],

            Weight,

            [Price Type],

            max(Count) as MaxVal

          Resident _temp_Data

          Group By [Dress Type],

            Weight,

            [Price Type];

           

          Data:

          NoConcatenate LOAD

            [Dress Type],

            Weight,

            [Price Type],

            Price,

            Count

          Resident _temp_Data

          Where Count = MaxVal;

           

          DROP Table _temp_Data;

          • Re: How to Achieve the logic in the script
            Massimo Grossi

            try the attachment

             

            1.jpg

            • Re: How to Achieve the logic in the script
              Anand Chouhan

              Hi,

               

              Try to load the table this way and find max by help of the key

               

              T1:

              LOAD

              [Dress Type]&'_'&Weight&'_'&[Price Type]&'_'&Price as Key,*;

              LOAD * INLINE [

                  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   ];

               

              Left Join

               

              LOAD

              Key,

              Count(Key) as Count

              Resident T1

              Group By Key;

               

              Left Join

               

              LOAD

              [Dress Type],[Price Type],

              Max(Count) as Max

              Resident T1

              Group By [Dress Type],[Price Type];

               

              NoConcatenate

              Final:

              LOAD * Resident T1 Where Max = Count;

              DROP Table T1;

               

              Most Occured value.PNG

               

              Regards,

              Anand