topic Re: Need script advice in QlikView

Need script advice

Sat, 03 Sep 2011 07:59:56 GMT

Hi all

I have simple fields in my script as follows:

[Purchase order document]

[Purchase order item]

[Purchase order document]&'/'&[Purchase order Item] As [PO_Item]

[Item price]/[Item quantity] As [Price], This give me the unit price of a line item from table POs

In the script there is also another table with the following:

[Invoice receipt],

[PO Number],

[PO Number]&'/'&[Item] As [PO_Item]

[Invoice Quantity]

So in my table i have an expression to get the full amount of PO invoiced as following

Dimension: [PO_Item]

Expression: Sum[Invoice Quantity]*[Price]

This works fine in all cases except with PO numbers beginning with 41*, as they dont register quantity.

What i would need is a calculation in the script which does the calculation

[Item price]/[Item quantity] As [Price], but when a PO begins with 41*, it devides the [Item price] with the amount of invoice receipts (rows).

Sorry for the long, and bad explanation..

Hopefully someone can support.

Re: Need script advice

its_anandrjs — Sat, 03 Sep 2011 08:08:58 GMT

Hi,

As i underastand your data is some thing like 41* and the calculation not work for this i am right or wrong i am not sure. If you have any sample file if so please provide in case is 41* is not aceptable so remove it by command Purgechar( ) to remove such symbols ( * ) like =Purgechar( Yourcolumn,' * ' ).

Regards,

Anand

Re: Need script advice

Sat, 03 Sep 2011 09:08:43 GMT

Hi,

I've attached a sample file as well as an example of the data files.

Normal purchasing documents record their total value as such:> Item * quantity= Item price USD

But purchasing documents 41***** arent recorder in such way. instead they have several deliveries but the quantity value is still 1. So using the same formula with POs beginning with 41** actually multiplies the total sum by the count of invoice receipts.

Please see the file attached, it should give a good idea what's wrong.

-Purchase Orders.xls represents the PO total values per line items

-Invoice_Receipts represent the invoices posted for those Purchase orders.

Thanks for the help