Scripting Question

[#1]

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	unit price	piece quantity	total price	if(carton quantity = piece quantity,'0','1')
cup	1	Chicken 4x	54	0	54	0	0
cup	2	Beef 9x	5	0	45	0	1
cup	2	Shrimp 9x	5	0	45	0	1
bag	3	Fish 5x	20	0	20	0	0
bag	4	Curry 7x	55	0	20	0	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.

[#2]

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

[Anonymous]

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!

[#4]

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.

[#5]

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.

[sasikanth]

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

[sasikanth]

Try this Expression

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

[#8]

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.

[sasikanth]

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 .

[#10]

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.