topic Re: creating a new field in the script by using several fields from different data sources in QlikView

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

thanhphongle — Thu, 28 Sep 2017 14:48:48 GMT

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)

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

vvira1316 — Thu, 28 Sep 2017 14:52:29 GMT

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?

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

Colin-Albert — Thu, 28 Sep 2017 15:01:54 GMT

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)

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

thanhphongle — Thu, 28 Sep 2017 15:02:12 GMT

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

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

Colin-Albert — Thu, 28 Sep 2017 15:05:16 GMT

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.

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

vvira1316 — Thu, 28 Sep 2017 15:08:35 GMT

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

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

thanhphongle — Thu, 28 Sep 2017 15:19:49 GMT

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;

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

Colin-Albert — Thu, 28 Sep 2017 15:25:46 GMT

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)

;

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

thanhphongle — Thu, 28 Sep 2017 15:33:24 GMT

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

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

vvira1316 — Thu, 28 Sep 2017 16:54:54 GMT

Hi,

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

Thanks & Regards,

Vijay