Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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 ....;
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
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