Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alvinford
Contributor III
Contributor III

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   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.

5 Replies
Anonymous
Not applicable

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;

maxgro
MVP
MVP

try the attachment

1.jpg

Anonymous
Not applicable

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."

its_anandrjs

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

maxgro
MVP
MVP

may be you're right

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