topic Re: need help on comparison like ARRAY in QlikView

need help on comparison like ARRAY

sarfaraz_sheikh — Wed, 13 Dec 2017 15:58:00 GMT

Hello Folks,

I am having the below data from that i have to count the potential error and below the logic to implement:

ProductName	IndicationName	PackageName	Price
Zytiga	mCRPC post chemotherapy	XYZ	200
Zytiga	mCRPC post chemotherapy	ABC	100
Zytiga	mCRPC post chemotherapy	EFG	800
Zytiga	mCRPC post chemotherapy	HIJ	600
Zytiga	mCRPC post chemotherapy	KML	500
Zytiga	MCRPC pre-chemo (post ADT)	DNA	500
Zytiga	MCRPC pre-chemo (post ADT)	KLC	100
Zytiga	MCRPC pre-chemo (post ADT)	KYT	200
Zytiga	MCRPC pre-chemo	LPG	330
Zytiga	MCRPC pre-chemo	OCG	400
Zytiga	MCRPC pre-chemo	DMY	500
Zytiga	MCRPC pre-chemo	KPC	900

Price of every PackageName of every IndicationName is gets compared with Price of another indicationName and PackageName and if Price Matches then it should count it as 1 if not matches then count it as 0

For Example:

[mCRPC post chemotherapy] [XYZ] [200] = [MCRPC pre-chemo (post ADT)] [DNA] [500] ------0 (prices are not Equal)

[mCRPC post chemotherapy] [XYZ] [200] = [MCRPC pre-chemo (post ADT)] [KLC] [100] ------0 (prices are not Equal)

[mCRPC post chemotherapy] [XYZ] [200] = [MCRPC pre-chemo (post ADT)] [KYT] [200] ------1 (prices are Equal )

[mCRPC post chemotherapy] [ABC] [100] = [MCRPC pre-chemo (post ADT)] [DNA] [500] ------0 (prices are not Equal)

[mCRPC post chemotherapy] [ABC] [100] = [MCRPC pre-chemo (post ADT)] [KLC] [100] ------1 (prices are Equal )

[mCRPC post chemotherapy] [ABC] [100] = [MCRPC pre-chemo (post ADT)] [KYT] [200] ------0 (prices are not Equal)

and so on

similarly for second indication with third one

[MCRPC pre-chemo (post ADT)] [DNA] [500] = [MCRPC pre-chemo] [LPG] [330] -----------0 (prices are not Equal)

[MCRPC pre-chemo (post ADT)] [DNA] [500] = [MCRPC pre-chemo] [OCG] [400] -----------0 (prices are not Equal)

[MCRPC pre-chemo (post ADT)] [DNA] [500] = [MCRPC pre-chemo] [DMY] [500] -----------1 (prices are Equal )

and this continues till condition becomes false

The above example i have shown you for one product called 'ZYTIGA' but we have multiple products available in our reports and for every product we have to do the same.

I am looking for best possible way from optimization point of view. Please help me to implement this.

Help would be much appericiated.

hic‌ gwassenaar‌ please comment.

Thanks

Sarfaraz

+91 7620140822 / 9967616445

Re: need help on comparison like ARRAY

marcus_sommer — Wed, 13 Dec 2017 16:40:17 GMT

I think you will need some kind of logic which records respectively values should be compared against eachother. Does any exists?

- Marcus

Re: need help on comparison like ARRAY

sarfaraz_sheikh — Wed, 13 Dec 2017 16:54:17 GMT

Hello Marcus,

Thanks for your reply.

I need logic for this as I am trying to do it but got stuck how to implement it.

Please help me with the logic.

Thanks

Sarfaraz

Re: need help on comparison like ARRAY

marcus_sommer — Wed, 13 Dec 2017 17:26:42 GMT

There must be a business-logic behind the requirement respectively at least a aim to what should gained from this flagging/counting. If you don't know it you will need to ask someone in your company.

- Marcus

Re: need help on comparison like ARRAY

MarcoWedel — Wed, 13 Dec 2017 23:29:51 GMT

Hi,

maybe one solution might be:

table1:
LOAD RecNo() as ID, * FROM [https://community.qlik.com/thread/284956] (html, codepage is 1252, embedded labels, table is @1);

QUALIFY *;
UNQUALIFY ProductName;

Join
Comparison:
LOAD * Resident table1;

UNQUALIFY *;

Join
LOAD ID, Comparison.ID,
    -(IndicationName=Comparison.IndicationName) as IsEqualIndicationName,
    -(PackageName=Comparison.PackageName) as IsEqualPackageName,
    -(Price=Comparison.Price) as IsEqualPrice,
    -(ID=Comparison.ID) as IsEqualID
Resident table1;

hope this helps

regards

Marco