topic Re: Add a value in the script in QlikView

Add a value in the script

guillaume_gorli — Thu, 30 Jun 2016 18:18:24 GMT

Dear All,

I have an issue regarding a specific value i would like to link to a variable directly in the script of my application.

let me explain :

My test database is :

Date

Part Number

Cost

Qty

Value

The issue is that the cost for part number A is set up as zero in the database (it is set up like this directly in my ERP) although i need this specific part number to be linked with a different cost than the one initially coming from the ERP.

Please note the cost of this part number A is changing every year.

For instance, we could imagine that part number A should be "manually" costed 5 in 2015 and 7 in 2016.

As of now the output i got in the qvw is as follow :

Although what i a looking for is the following :

I would like to set this up directly in the script.

For the ones who want to help, i join the qvw as well as the test xls database

Thanks in advance

Guillaume

Re: Add a value in the script

Marcio_Campestrini — Thu, 30 Jun 2016 18:24:17 GMT

Guillaumne

Try to read from your database the information and after read an Excel file with the cost for part A for every month. After this, create a new table with the information from part A without cost value, and join the cost value from the sheet.

Re: Add a value in the script

sunny_talwar — Thu, 30 Jun 2016 18:31:48 GMT

May be this?

Script additions:

Join(Feuil1)

LOAD [Part Number],

MonthName(MonthYear) as MonthYear,

Cost1

Inline [

Part Number, MonthYear, Cost1

A, 01/12/2015, 5

A, 01/01/2016, 7

];

Expression2: =Num(Alt(Cost1,Cost))

Expression3: =Sum (Qty * Alt(Cost1, Cost))

Re: Add a value in the script

maxgro — Thu, 30 Jun 2016 18:57:13 GMT

..........

/// TEST DATABASE ///

LOAD Date,

[Part Number],

if(Year(Date)=2015 and [Part Number]='A', 5,

if(Year(Date)=2016 and [Part Number]='A', 7,

Cost)) as Cost,

//Cost,

Qty,

Value

FROM

...............

Re: Add a value in the script

guillaume_gorli — Mon, 04 Jul 2016 08:13:46 GMT

Sunny,

i am wrong somewhere but i can't figure out where.

i got below message error when i try to load part of your script in my complete app :

i spent some time trying to understand where i am wrong in my script without no luck.

Any idea ?

Guillaume

Re: Add a value in the script

guillaume_gorli — Mon, 04 Jul 2016 08:15:53 GMT

Max,

What is the expression used to determined "Value" ?

Could you upload the qvw you built so that i could check ?

Guillaume

Re: Add a value in the script

Peter_Cammaert — Mon, 04 Jul 2016 10:32:43 GMT

I think an ApplyMap() trick will offer more flexibility. Although it's not a ready to use script for you, this explains the idea:

MapCostExceptions:

LOAD [Product Number] & '-' & Année AS Index,

Cost

FROM MyCostExcel.xlsx (ooxml, ...);

Facts: // ApplyMap will 'update' the cost if P & Y found in mapping table

LOAD Date,

[Product Number],

Qty,

Value,

ApplyMap('MapCostExceptions', [Product Number] & '-' & Year(Date), Cost)

AS Cost,

FROM YourOtherSource (...);

Best,

Peter

Re: Add a value in the script

guillaume_gorli — Mon, 04 Jul 2016 10:38:51 GMT

Thanks you guys for your support. i am realizing i am facing other issus in my app and then the questions raised were not properly asked. I will close this thread and open a new one trying to be more accurate.

Thanks

Guillaume

Re: Add a value in the script

MayilVahanan — Mon, 04 Jul 2016 10:40:24 GMT

1. Need to mention the Table Name

2. Date column is not available in "InLine Table". You need to change "Date -> MonthYear".

PFA