topic Re: Load Script Problem in QlikView

Load Script Problem

Thu, 18 Feb 2016 10:12:04 GMT

Hi,

I have an issue and I dont know how to solve this.

I have three tables. One Table with assets, one table with the different books (rules) and one table with all Transactions regarding the assets.

Now I Need to define the value-field of efa_assettrans

I want to create a new value-field called additions, but the Problem is the assetstatus (defines the Status for the whole asset, not only for Transactions, therefore it is in this table).

The field value is only correct, when assetstatus in efa_asset is <> '1490'

Now I want to say:

if(assetstatus<>'1490'[in efa_asset], value,0) as Additions,

How can this work?

Thank you very much in advance.

Chris

Re: Load Script Problem

Chanty4u — Thu, 18 Feb 2016 10:18:22 GMT

=if(assetstatus<>'1490',[in efa_asset], value,0) as Additions,

=Count(DISTINCT if((assetstatus<>'1490',[in efa_asset], value,0) as Additions,

Re: Load Script Problem

PrashantSangle — Thu, 18 Feb 2016 10:19:56 GMT

Hi,

try using not match() or not wildmatch()

if(not match(fieldName,FieldValue),Value,0)

Regards

Re: Load Script Problem

Thu, 18 Feb 2016 11:24:55 GMT

Hi,

thanks for you suggestions but both did not work, unfortunately (or I did mistakes ).

I will attached the qvw-file. Hopefully someone can try and solve it.

Chris

Re: Load Script Problem

sasikanth — Thu, 18 Feb 2016 11:35:36 GMT

The Original name of the assetstatus seems like STATUS,

try this code

IF ( not wildmatch(STATUS,'*1490*'),STATUS,0)

Re: Load Script Problem

sunny_talwar — Thu, 18 Feb 2016 11:36:22 GMT

May be this:

MappingTable1:

Mapping

LOAD AnlagenNr,

assetstatus

Resident efa_asset;

MappingTable2:

Mapping

LOAD #AssetBookID,

AnlagenNr

Resident efa_assetbook;

//************************************************

// efa_assettrans : Anlagenbewegungen

//************************************************

efa_assettrans:

LOAD *,

If(ApplyMap('MappingTable1', ApplyMap('MappingTable2', #AssetBookID)) = 1409, 0, ApplyMap('MappingTable1', ApplyMap('MappingTable2', #AssetBookID))) as assetstatus1;

LOAD

autonumber(CMPCODE&'-'&BOOKCODE&'-'&ASSETCODE) AS #AssetBookID,

autonumber(CMPCODE&'-'&BOOKCODE&'-'&ASSETCODE&'-'&TXNYEAR&'-'&TXNPER&'-'&TXNDATATYPE&'-'&EVENTTYPE&'-'&RUNNUM) AS #AssetTransID,

// autonumber(CMPCODE&'-'&ASSETCODE) AS #AssetID,

RUNNUM as runnum,

TXNNUM as txnnum,

TXNDATETIME as txndatetime,

// ASSETCODE as AnlagenNr,

// ASSETCODE,

USRNAME as usrname,

LINKEDASSET as linkedasset,

DEPNCODE as depncode,

BOOKCODE as Bestand_Trans,

// CMPCODE as cmpcode,

DOCCODE as doccode,

DOCNUM as docnum,

DOCLINENUM as doclinenum,

TXNDATATYPE as txndatatype,

EVENTTYPE as eventtype,

TXNYEAR as txnyear,

TXNPER as txnper,

VALUE as value,

RTRANSYR as rtransyr, //Year, the Assets-Aktion belongs to

RTRANSPER as rtransper //Period, the Assets-Aktion belongs to

;

SQL SELECT *

FROM "EFA_ASSETTRANS"

WHERE CMPCODE like '$(varCMPCODE_SQL)';

store efa_assettrans into $(DirDataQVD)efa_assettrans.qvd;

Re: Load Script Problem

kaushiknsolanki — Thu, 18 Feb 2016 11:36:35 GMT

Hi,

Include below statements in your script after the store statement on efa_assettrans tab.

Left join(efa_assettrans)

Load Distinct #AssetBookID , AnlagenNr as Key Resident efa_assetbook;

Left join(efa_assettrans)

Load Distinct assetstatus as AssetStatus , AnlagenNr as Key Resident efa_asset;

efa_assettrans_Final:

Load *,if(AssetStatus <> 1490,Value,0) as Addition

Resident efa_assettrans;

Drop table efa_assettrans;

Regards,

Kaushik Solanki