9 Replies Latest reply: Sep 16, 2016 7:15 AM by Michael Behrend

# Scripting Question

Hello Comunity i have a Question and a problem,

at the moment i have a Pivot table with this informations

Groupdocument numberArtikelcarton quantityunit pricepiece quantitytotal priceif(carton quantity = piece quantity,'0','1')
cup1Chicken 4x5405400
cup 2Beef 9x504501
cup2Shrimp 9x504501
bag3Fish 5x2002000
bag4Curry 7x5502000

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

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

The expressions from the pivot is.

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

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

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

Try this Expression

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

• ###### Re: Scripting Question

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

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

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

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.