5 Replies Latest reply: Mar 24, 2016 10:52 AM by Stefan Wühl RSS

    load script with condition

    Christian Wylezol

      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
          Stefan Wühl

          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
              Christian Wylezol

              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
                  Stefan Wühl

                  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
                      Christian Wylezol

                      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
                          Stefan Wühl

                          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