creating a new field in the script by using several fields from different data sources

[thanhphongle]

Hello community,

I want to create a new field called fi_pv_reg in the script by calculating several fields from different data sources

here is an exmaple of my table

MAP_INTEREST:

MAPPING LOAD

     fi_interest_period,

     fi_interest

FROM

main.xlsx

(ooxml, embedded labels, table is finance);

Data:

LOAD *,

          PV(fi_interest/12, contract_term_reg, fi_rate_reg, 0, 1)*-1 as fi_pv_reg;

Directory;

LOAD main_unique_id,

     customer

     asset_id,

     asset_model,

     contract_term_reg,

     fi_rate_reg

FROM

main.xlsx

(ooxml, embedded labels, table is main)

QV returns me an error message

Field can not be found - <fi_interest>

LOAD main_unique_id,

     customer,

     asset_model,

     contract_term_reg,

     fi_rate_reg,

FROM

lease.one_import.xlsx

(ooxml, embedded labels, table is main)

[vvira1316]

HI,

You should use

Data:

LOAD *,

          PV(fi_interest/12, contract_term_reg, fi_rate_reg, 0, 1)*-1 as fi_pv_reg

Resident MAP_INTEREST; (I see that other field is from different table, so my apology)

Would you be able to share sample data/script?

[Colin-Albert]

You have created a mapping table, but do not have an applymap statement to convert a date or period to a fi_interest value using the mapping table in your script.

You need to add a line something like the  example below  but using the correct period or date field

Directory;

LOAD

     main_unique_id,

     customer

     asset_id,

     asset_model,

     contract_term_reg,

     fi_rate_reg,

     applymap('MAP_INTEREST', YOUR-PERIOD-DATA-FIELD, 0) AS fi_interest

FROM

main.xlsx

(ooxml, embedded labels, table is main)

[thanhphongle]

actually I just posted the my whole script. If you mean sharing my qv-file, unfortunetly I can't share it

[Colin-Albert]

Hi Vijay,

Your solution will not work as the MAP_INTEREST table does not contain the fields contract_term_reg and fi_rate_reg.

The expression requires fi_interest, contract_term_reg and fi_rate_reg to exist in the same table.

See my reply that includes the applymap statement.

[vvira1316]

Hi Colin,

Yeah, I realized it later that it was coming from a different table and I was also wondering about mapping load.

Thanks for pointing it out.

BR,

Vijay

[thanhphongle]

Thx Colin for your help,

I tried another solution but I m not sure if its correct. So far I tested it and it has loaded the script correctly and the calculations are fine.

LOAD fi_rate_reg,

     contract_term_reg,

     fi_interest,

     PV(fi_interest/12, contract_term_reg, fi_rate_reg, 0, 1)*-1 as fi_pv_perc_reg

Resident Data;

[Colin-Albert · Accepted solution]

Does the fi_interest field exist on the main sheet of main.xlsx  ?

If so, you do not need the resident load, just add the expression to your Data load script

Data:

LOAD main_unique_id,

     customer

     asset_id,

     asset_model,

     contract_term_reg,

     fi_rate_reg,

     fi_interest,

     PV(fi_interest/12, contract_term_reg, fi_rate_reg, 0, 1)*-1 as fi_pv_perc_reg

FROM

main.xlsx

(ooxml, embedded labels, table is main)

;

[thanhphongle]

No, it doesnt exist on the main sheet of main.xlsx

[vvira1316]

Hi,

If you have it working. Can you please close the thread.

Thanks & Regards,

Vijay