Add a value in the script

[guillaume_gorli]

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

[Marcio_Campestrini]

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.

[sunny_talwar]

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))

[maxgro]

..........

/// 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

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

[guillaume_gorli]

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

[guillaume_gorli]

Max,

What is the expression used to determined  "Value" ?

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

Guillaume

[Peter_Cammaert]

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

[guillaume_gorli]

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

[MayilVahanan]

Hi

1. Need to mention the Table Name

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

PFA