Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Very thanks in advance.
Regards,
Alvin.
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;
try the attachment
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."
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;
Regards,
Anand
may be you're right
it seems that all the solutions so far are similar, what changes are the fields of the group by