Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
alvinford
New 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.

Tags (1)
5 Replies
chris_deniziak
Contributor

Re: How to Achieve the logic in the script

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;

MVP
MVP

Re: How to Achieve the logic in the script

try the attachment

1.jpg

chris_deniziak
Contributor

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

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

MVP
MVP

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

Community Browser