topic Re: Code Help?? in QlikView

Code Help??

rohitians — Thu, 10 Apr 2014 10:58:35 GMT

Hello Everyone,

I have following code,

=fabs(SUM(AGGR(if(( (vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181)),SUM({$<ItemType_Name={'Raw Material'}>}

Inv_Val)),Location_Code, Item_Code, [Lot No_])))

Now the thing is i only want to display top 300 items on this list ,

I have decided to use rank on the above expression so that i would be able to pick top 300 items,

how would i frame the same...

please help..

Thanks,

Rohit

Re: Code Help??

Thu, 10 Apr 2014 11:05:08 GMT

Use the dimension limits instead of rank.On the dimensions limits tab, choose Show only and type in 300 and you get the to 300 by size.

Re: Code Help??

rohitians — Thu, 10 Apr 2014 11:21:31 GMT

I have items whose inventory are present at different locations, and that is making my life tough now,

I want to sort the following table

and th expression for thr expression column is

=fabs(SUM(AGGR(if(( (vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181)),SUM({$<ItemType_Name={'Raw Material'}>}

Inv_Val)),Location_Code, Item_Code, [Lot No_]))) and the table is

Item Name	Location	> 181 Days
Total		42,873,369
Paracetamol ( Gal) B.P - Ziejan Kangle	MFG-GOA I	3,284,960
Paracetamol ( Gal) B.P - Ziejan Kangle	MFG-GOA IV	0
Paracetamol ( Gal) B.P - Ziejan Kangle	MFG-BADDI I	46,510
Paracetamol ( Gal) B.P - Ziejan Kangle	TOOLS-R&D	0
Hyprellose(Methocel K15 M Premium) Ph.Eur	MFG-GOA I	35,135
Hyprellose(Methocel K15 M Premium) Ph.Eur	MFG-GOA IV	2,506,455
Hyprellose(Methocel K15 M Premium) Ph.Eur	MFG-BADDI I	0
(+)DIP CHLORIDE IN HEXANE	MFG-PATALGANGA	1,868,987
Timolol Maleate U.S.P	MFG-GOA II	1,666,298
Timolol Maleate U.S.P	TOOLS-R&D	0
ALLOPURINOL BP	MFG-GOA IV	1,567,681
Aripiprazole	MFG-GOA I	1,197,519
Bromfenac Sodium sesquihydrate Inhouse	MFG-GOA II	0
Bromfenac Sodium sesquihydrate Inhouse	MFG-GOA II PROJECTS	1,195,342
DORZOLAMIDE HYDROCHLORIDE USP	MFG-GOA II	0
DORZOLAMIDE HYDROCHLORIDE USP	MFG-GOA II PROJECTS	1,148,348
RASAGILINE MESYLATE IN HOUSE	MFG-GOA IV PROJECTS	1,117,050
H.P.M.C. E-5(Hypromellose) E.P	MFG-GOA I	29,614
H.P.M.C. E-5(Hypromellose) E.P	MFG-GOA IV	819,007
H.P.M.C. E-5(Hypromellose) E.P	MFG-BADDI I	6,472
TROPINE	MFG-RABALE API	795,286
Olanzapine Form-I USP	MFG-GOA I	0
Olanzapine Form-I USP	MFG-GOA IV	0
Olanzapine Form-I USP	MFG-GOA IV PROJECTS	747,519
SIMVASTATIN Ph.Eur (BIOCON)	MFG-GOA I	733,388
ADAPALENE USP	MFG-GOA I	697,030
Peg-5 Glycerol Sterate- Arlatone -983s Inhouse	MFG-GOA I	674,943
3(CARBAMOYL-METHYL)-5-METHYLHEXAMIC ACID	MFG-RABALE API	618,005
3(CARBAMOYL-METHYL)-5-METHYLHEXAMIC ACID	LLM-INNOVATIVE	0
Metformin Hydrochloride Ph.Eur	MFG-GOA I	487,917
Metformin Hydrochloride Ph.Eur	MFG-GOA IV	0
Metformin Hydrochloride Ph.Eur	MFG-BADDI I	105,434
(6S)-6-METHYL-4-OX-5,6-DIHYDRO-4H-THIAO[(2,3-B)	MFG-RABALE API	549,213
(6S)-6-METHYL-4-OX-5,6-DIHYDRO-4H-THIAO[(2,3-B)	MFG-PATALGANGA	0
ALLOPURINOL PH.EUR	MFG-GOA I	486,853
ALLOPURINOL PH.EUR	MFG-GOA IV	0
ALLOPURINOL PH.EUR	MFG-GOA IV PROJECTS	11,551
POVIDONE ( PVPK-25 ) I.P	MFG-WALUJ	23,042
POVIDONE ( PVPK-25 ) I.P	MFG-BADDI I	0
POVIDONE ( PVPK-25 ) I.P	MFG-BADDI II	468,172
SPENT PALLADIUM ON CARBON	MFG-PATALGANGA	0
SPENT PALLADIUM ON CARBON	MFG-KILO LAB	470,526
SPENT PALLADIUM ON CARBON	LLM-HINDUSTAN	0
Homatropin Hydrobromide I.P.	MFG-GOA II	458,332
Homatropin Hydrobromide I.P.	TOOLS-R&D	0
Homatropin Hydrobromide I.P.	NORRIS MEDICINES LTD (VIRTUAL)	0
P.V.P.K-30 (Shweta Pharma/Novo) B.P	MFG-GOA I	444,289
P.V.P.K-30 (Shweta Pharma/Novo) B.P	MFG-BADDI I	0
TRANS - 4 - METHYL CYCLOHEXYL AMINE	MFG-RABALE API	409,890
Sepifilm (752 White) For Citalopram - Kent InHouse	MFG-GOA I	388,547
Fluoxetine Hydrochloride Ph.Eur	MFG-GOA I	367,622
Propylene Glycol IP/BP	MFG-GOA I	775
Propylene Glycol IP/BP	MFG-GOA IV	0
Propylene Glycol IP/BP	MFG-GOA II	4,824
Propylene Glycol IP/BP	MFG-WALUJ	2,528
Propylene Glycol IP/BP	MFG-BADDI I	1,775
Propylene Glycol IP/BP	MFG-BADDI II	48,855
Propylene Glycol IP/BP	LLV-LESANTO	238,866
Propylene Glycol IP/BP	LLV-SAKAR	41,961
Propylene Glycol IP/BP	LLV-RAHUL	248
Propylene Glycol IP/BP	LLV-DCI	0
Propylene Glycol IP/BP	IN-TRANSIT	0

Re: Code Help??

Thu, 10 Apr 2014 11:48:27 GMT

You can choose which dimension you want the limit on in the dimension limits settings. -John

Re: Code Help??

rohitians — Thu, 10 Apr 2014 12:39:20 GMT

Hey John,I know that option, but it has a limit upto 100,

I want to show top 300 values..

=aggr(if(Rank(fabs(SUM(AGGR(if(( (vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181)),SUM({$<ItemType_Name={'Raw Material'}>}

Inv_Val)),Location_Code, Item_Code, [Lot No_]))))<300,Item_Name),Item_Name,Location_Name,Location_Name_Short)

Above code i am using But aint working..

Re: Code Help??

vadimtsushko — Thu, 10 Apr 2014 13:51:14 GMT

Are you using that formula as calculated dimension, or as expression? It is not clear.

From the look at that formula it seems to me you are trying to return several fields (dimensions) from external aggr() as a set of calculated dimensions. If so - I believe that is impossible.

But you can achieve same result another way:

Create calculated dimension by a rank function. Formula for that calculated dimension would be something like

Rank(if((vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181),SUM({$<ItemType_Name={'Raw Material'}>} Inv_Val)),Location_Code, Item_Code, [Lot No_]). That dimension will return numeric rank for each row

Make that dimension invisible

Set dimension limit for that dimension to 300 minimal values

Ordinary dimensions (Location_name, Item_Name) remains unchanged