topic Re: How to Achieve the logic in the script in QlikView

How to Achieve the logic in the script

alvinford — Thu, 19 Feb 2015 18:11:30 GMT

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.

Re: How to Achieve the logic in the script

Anonymous — Thu, 19 Feb 2015 18:25:48 GMT

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

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

maxgro — Thu, 19 Feb 2015 18:53:19 GMT

try the attachment

Re: How to Achieve the logic in the script

Anonymous — Thu, 19 Feb 2015 19:06:53 GMT

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

its_anandrjs — Thu, 19 Feb 2015 19:07:07 GMT

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

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

Re: How to Achieve the logic in the script

maxgro — Thu, 19 Feb 2015 19:26:49 GMT

may be you're right

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