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

# How to Achieve the logic in the script

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

Hi Alvin,

Try this out:

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

];

_temp_Data:

[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)

[Dress Type],

Weight,

[Price Type],

max(Count) as MaxVal

Resident _temp_Data

Group By [Dress Type],

Weight,

[Price Type];

Data:

[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

try the attachment

• ###### Re: How to Achieve the logic in the script

Your script will only work if the Dress Type only has one weight and one price type. He specifically stated he needs it "Based on the column Dress Type, Weight , Price Type and Price."

• ###### Re: How to Achieve the logic in the script

may be you're right

it seems that all the solutions so far are similar, what changes are the fields of the group by

• ###### Re: How to Achieve the logic in the script

Hi,

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

T1:

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

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

Key,

Count(Key) as Count

Resident T1

Group By Key;

Left Join

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

Regards,

Anand