Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Formula in Load Script

Hi everybody,

I hope someone of you can help me.

I have a table with asset Transactions. Now I have to separate the field value in the script.

i.e.

If(txngroup='COST', value) as acquisition_cost

Does someone of you maybe have an idea?

Thanks

Chris

1 Solution

Accepted Solutions
mark6505
Partner - Master
Partner - Master

Hi Chris,

I would say you are nearly there.

I would advise you keep the  AssetTransID as this seems to be the key field.

If you then want a combine cost be assets you would want a aggrate table something like

ValueLoad:

Load

     AssetBookID

     IF(Txngroup='Cost', Sum(Value)     as acquistion_costs,

     ...

     ..

Resident Asset_transactions

Group by AssetBookID;

Mark

View solution in original post

7 Replies
kaushiknsolanki

Hi Chris,

For this you will have to join the "Asset Transaction Mapping Det" Table with "Asset Transaction" Table first and then after join you can use resident load to write your condition, same as what you have written.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
sathishkumar_go
Partner - Specialist
Partner - Specialist

Hi,

You can able to do by 2 ways

1) create a mapping file

TXNGROUP_Mapping:

mapping

load

txndatatype,

txngroup

resident asset_txn_mapping_det where txngroup = 'COST';

2) use apply map function

if(applymap('TXNGROUP_Mapping',txndatatype,0) = 'COST', value) as acquisition_cost

in Asset_Transaction table

-Sathish

sathishkumar_go
Partner - Specialist
Partner - Specialist

another way:

use left join with asset_txn_mapping_det to Asset_Transaction table

after that,

Asset_Transaction table_Temp:

load

*,

If(txngroup='COST', value) as acquisition_cost

resident Asset_Transaction table;

drop table Asset_Transaction table;

-Sathish

Not applicable
Author

Hi everybody,

thanks for the quick Responses. I chose to use the left join but this does not work.

But I have two Problems maybe due to same reason:

The field Value has -675000, due to txngroup = 'COST', the field acquistion_costs should also have -675000, but has -2325000

The field depreciation_costs should have a Zero (0,00)

the Field Value has -11250, due to txngroup = 'DEPN' the field acquisition_costs should have a Zero, but has a value in it.

The field depreciation_costs is null but should have the same entry as the field value (-11250)

What is my mistake? How can I get this to work?

This is my Script:

Thanks

Chris

mark6505
Partner - Master
Partner - Master

Hi Chris,

I would say you are nearly there.

I would advise you keep the  AssetTransID as this seems to be the key field.

If you then want a combine cost be assets you would want a aggrate table something like

ValueLoad:

Load

     AssetBookID

     IF(Txngroup='Cost', Sum(Value)     as acquistion_costs,

     ...

     ..

Resident Asset_transactions

Group by AssetBookID;

Mark

sathishkumar_go
Partner - Specialist
Partner - Specialist

HI,

"AnlageNR" field is coming from which table? what is the link between "AnlageNR" field table and Value load?

-Sathsih

mato32188
Specialist
Specialist

Hello Christian,

AssetTransaction:

LOAD

          #AssetBookID,

          txndatatype as DataTypeID,    

          eventtype as EventTypeID,

          value

FROM efa.assettrans.qvd;

AssetTransactionGroup:

LOAD

          Tcode as DataTypeID,

          IF(Txngroup = 'COST', 'AquisitionCost',

               IF(Txngroup = 'DEPN', 'DepreciationCost',

                    IF(Txngroup = 'DISP', 'Disposals',

                         IF(Txngroup = 'TRA', 'Transfers', 'UnknownTransGroup') ))) as TransactionGroup

FROM assets_mapping.xlsx;

AssetEventGroup:

LOAD

          Ecode as EventTypeID,

          IF(match(EventGroup, 'DEPN', 'ADD', 'DEPC'), 'Addition', 'UnknownEventGroup') as EventGroup

FROM assets_mapping.xlsx;

Using a Set analysis in CHARTS, you can calculate any of your Group's values, ex.:

Sum( {<TransactionGroup = {'COST'}>} Value) as AquisitionCosts,

Sum( {<TransactionGroup = {'DEPN'}>} Value) as DepreciationCosts;

In table box use your desired fields.

Of course, fields could be prepared the way you mentioned in script by creation separated field values, but joins would be heavy operations.

Best regards,

Martin

ECG line chart is the most important visualization in your life.