topic Scripting Question in QlikView

Scripting Question

Fri, 09 Sep 2016 12:03:41 GMT

Hello Comunity i have a Question and a problem,

at the moment i have a Pivot table with this informations

Group	document number	Artikel	carton quantity	piece quantity	if(carton quantity = piece quantity,'0','1')
cup	1	Chicken 4x	54	54	0
cup	2	Beef 9x	5	45	1
cup	2	Shrimp 9x	5	45	1
bag	3	Fish 5x	20	20	0
bag	4	Curry 7x	55	20	0

this table is to look at artikel which went out for free.

Now i saw all artikel which have the same carton quantity and piece quantity are on a set.

The Set Artikel must be removed from the table because they dont go out for free the price is on the head from the set.

The artikel which have different numbers on carton quantity and piece quantity go out for free.

The different between Piece quantity and carton quantity is: Beef has 9 Cups in a Carton so we have 5 cartons with 9 cups = 45

importend is if(carton quantity = piece quantity,'0','1') this shows me which artikel has the same carton quantity and piece quantity.

Now my question: How can i FIlter it that i have only Artikel with different carton quantity and piece quantity, have a one on the end.

I need it to count the the Artikel which go out for free.

The informations are all from one Table.

Re: Scripting Question

Mon, 12 Sep 2016 20:03:47 GMT

Hello,

What are the expressions in your pivot table?

Is "Suppress Zero Values" ticked in Presentation tab of the pivot table?

Can't you maybe create a flag during data load and simply filter proper expression based on this flag? (using set analysis)

BR,

Kuba

Re: Scripting Question

Anonymous — Mon, 12 Sep 2016 20:18:28 GMT

Hello Michael,

i would move this "if(carton quantity = piece quantity,'0','1')" to the script as a flag like:

load
*,
if(carton quantity = piece quantity,'0','1') as _FlagBlaBla
from

And then sum(<_FlagBlaBla = {1}>} total_price) for example.

edit: always bear in mind: "Move all complexitiy you can to the script"

--> So for this example use Flags!

Re: Scripting Question

Wed, 14 Sep 2016 07:26:18 GMT

Hey thx the idea is great, but i have a problem i get my informations from a other data vie Binary.

i can't creat this flag because on the main data it's not working.

The carton quanitity is a fix information from the table but the piece quantity is from a other if statement

if(Upper(Einheit)='BUNDLE'or Upper(Einheit)='KARTON'or Upper(Einheit)='STÜCK',Mengetmp*Mengeneinheit,Mengetmp)as piece quantity

When i use if(carton quantity = piece quantity,'0','1') as Flag

i get an error he can't find the Table.

Re: Scripting Question

Wed, 14 Sep 2016 07:55:25 GMT

The expressions from the pivot is.

To see how many artikel go out for free.

The Document number explain it self, carton quantity is normaly the quntity how many cartons are in the order. Unit price is here 0 because i use a filter to show me all products with a unite price 0 normly it show's me the price from one carton. Pieces are the pieces in a carton. Total Price is the Price from all cartons in the order together to explain you have 5 cartons one carton costs 1€ also 5*1=5.

I found out when carton quantity and the piece quantity are the same, this artikel/order went out for free, when it's different the artikel/order didn't go out for free this artikel are on a set and this is the problem set artikel are shown with a unit/total price 0.

I saw it with this formel if(carton quantity = piece quantity,'0','1'

and now i will filter this but the problem is i get the informations from a other qlikview data via binary.

and i can't do a filter wth flag there.

Re: Scripting Question

sasikanth — Wed, 14 Sep 2016 08:24:27 GMT

HI,

If not able to do it from script then,

try this exp in place of Piecequantity

=sum(Aggr(if(cartonquantity=sum(piecequantity),sum(piecequantity)),Group,Artikel,cartonquantity,documentnumber))

Please find the attached application for reference

Re: Scripting Question

sasikanth — Thu, 15 Sep 2016 05:41:47 GMT

Try this Expression

sum(Aggr(if(cartonquantity*KeepChar(Artikel,'123456789') =sum(piecequantity),sum(piecequantity)),Group,Artikel,cartonquantity,documentnumber))

Re: Scripting Question

Thu, 15 Sep 2016 09:12:57 GMT

Hi thanks for you help,

but i have no idea how can this help me.

The think is Piecequantity is from a other if statement and these numbers are correct my main to look at the quantity. I compare the numbers in Piecequantity with an excell sheet where the right numbers are inside.

So i cant change

if(Upper(Einheit)='BUNDLE'or Upper(Einheit)='KARTON'or Upper(Einheit)='STÜCK',Mengetmp*Mengeneinheit,Mengetmp)as piece quantity

I need something what do this :

Sum piece quantity when carton quantity and piece quantity are not the same.

And i have no idea how i can do this.

Re: Scripting Question

sasikanth — Fri, 16 Sep 2016 06:45:23 GMT

HI,

your piece quantity is a caluculated fied from script and

you want to keep records where Piece quantity = Cartoon Quantity * Volume , so you can use my exp else share your sample app/data .

Re: Scripting Question

Fri, 16 Sep 2016 11:15:21 GMT

The problem is i cant show here the real script from our company. I i changed you exp on for ours but it shows me nothing the field is empty. I have no idea whta is wrong. I have on the pivot dimension produktgroup is an if to filter somethinks documentnumber is fix datum fix artikelnumber fix artikel fix then the fomels quantity karton is sum from one table Actual piece is this sum( {$} Menge) Brutto win is sum( {$} UmsatzBrutto) and this is all what i have. And now i have no idea how i can this filter.