topic Re: load script with condition in QlikView

load script with condition

Thu, 24 Mar 2016 11:18:44 GMT

Hi,

I have an issue with my load script.

I Need to create a flag in qlikview for selecting data correctly,

but the neccessary Information for that flag are in two Different tables

Our IT-Guy brought me this SQL, but it unfortunately does not work with Qlikview.

//at first: all entries get flag '1'
update efa_assettrans set TESTFLAG = 1;
//then: flag of entries with txndatatype ='12004' and status <> '1490' will be corrected to '0'
update efa_assettrans set TESTFLAG = 0
where TXNDATATYPE = 12004 and assetcode in
(select assetcode from efa_asset where status != 1490);

How can I realise this in load script of Qlikview that this flag will be included in the loaded efa_assettrans as a field?

Thanks for your help in advance.

Chris

Re: load script with condition

swuehl — Thu, 24 Mar 2016 11:27:08 GMT

Christian,could you post the QlikView script snippet you are using?

You would need to start the statement with SQL ... to use SQL in your script and then the SQL statements to the DBMS.

Besides this, I think you need to enable write access in QlikView's script editor Settings (at the bottom tab row) to allow writing to the DB.

Could you also detail on any error message you get or which exact problems you see?

Re: load script with condition

Thu, 24 Mar 2016 11:42:08 GMT

Hi Stefan,

I dont Need to write into the db. It was just a script from our IT-Department how they would do it in the database.

I "just" Need it as an additional field in Qlikview in my table "efa_assettrans"

i.e. if (...) as transflag

Do you have an idea, how the Syntax should look like, so that qlikview would do this?

Chris

Re: load script with condition

swuehl — Thu, 24 Mar 2016 11:47:36 GMT

You can do it in your load script like

CHECK:

LOAD

assetcode as assetcode_check

from efa_asset // adapt to your data source, e.g. qvd

where status != 1490;

RESULT:

LOAD

If( TXNDATATYPE = 2004 AND EXISTS(assetcode_check, assetcode),0,1) as FLAG,

...

FROM ....;

Re: load script with condition

Thu, 24 Mar 2016 14:42:06 GMT

Hi Stefan,

thanks. the check and result itself seem to work.

Just one last question: When I want to left join (asset_transactions) with the result, I got over 7 Million entries.

I already have 108 thousand entries and think after the join I should have the same.

Where is my mistake? (I just deactivated it, because it does not work)

CHECK:
LOAD
ASSETCODE as assetcode_check
FROM
$(DirDataQVD)efa_asset.qvd
(qvd)
where STATUS <> 1490
;

//left join (asset_transactions)
result:
LOAD
ASSETCODE as trans_asset_id,
If( TXNDATATYPE = '12004' AND EXISTS(assetcode_check, ASSETCODE),0,1) as flag

FROM
$(DirDataQVD)efa_assettrans.qvd
(qvd);

Thanks in advance and happy easter.

Chris

Re: load script with condition

swuehl — Thu, 24 Mar 2016 14:52:13 GMT

Your ASSETCODE is probably not a primary key in table efa_assettrans, that's why you load multiple records with ASSETCODE in the flag table and the JOIN will then multiply the records accordingly in the output table.

Maybe try

left join (asset_transactions)
result:
LOAD
ASSETCODE as trans_asset_id,

ASSETCODE as ASSETCODE_CHECK,
If( TXNDATATYPE = '12004' AND EXISTS(assetcode_check, ASSETCODE),0,1) as flag

FROM
$(DirDataQVD)efa_assettrans.qvd
(qvd)

WHERE NOT EXISTS(ASSETCODE_CHECK, ASSETCODE);

DROP FIELD ASSETCODE_CHECK;

I would not use a DISTINCT qualifier here because the DISTINCT qualifier will be inherited to the JOINed ouput table, making the complete transaction table distinct.

Happy easter to you, too.

Stefan