4 Replies Latest reply: Oct 18, 2016 7:14 PM by Allan Wilesmith RSS

    Update but only if source field is not null



      i have loaded data from a csv file which gets updated regularly. I would like to do an incremental update to qlikview but don't overweite field values if source is null. For instance



      CSV file

      ASSET    USER


      2                 Jill


      incremental update with expected result:

      ASSET   USER

      1              John

      2               Jill


      primary key is asset. So basically I want to update the table but don't overwrite USER if source is null keeping previous value also inserting new records. I have discovered how to update insert new records but cant figure out how to update existing records if user column is not null


        • Re: Update but only if source field is not null
          Sunny Talwar

          May be like this:




          FROM CSV File

          Where Len(Trim(USER)) = 0;



          LOAD *

          FROM CSV File

          Where Len(Trim(USER)) > 0;



          LOAD *


          Where Exists(ASSET_CHECK, ASSET);

            • Re: Update but only if source field is not null

              Hi thanks for replying! not sure if i follow that exactly. what i have so far is this


              LOAD [IP Address],

                  [MAC Address],

                  [Serial Number],

                  [Logged User],

                  [Response Time],

                  [Host Name],

                  [Last Scan],

                  [Last Seen],

                  [Installed Software],


                  [USB Printer]

              FROM [lib://netscanner/result.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);



              Concatenate (incremental)


              from [lib://Apps/discovery.QVD] (qvd) where not Exists([Host Name]);



              STORE incremental into [lib://Apps/discovery.QVD] (qvd);





              this will update all the records and insert but how do I stop overwriting [logged user] if the field is blank in the result.csv file?

                • Re: Update but only if source field is not null
                  Sunny Talwar

                  I am trying to think of ways you can do it, but I guess I am not sure I understand your requirement completely. Would you be able to provide some sample data with the expected output to help you better here?

                    • Re: Update but only if source field is not null

                      thanks for helping sunny!


                      basically I have a log file that gets over written everyday. what i want it to do is load this data into qlikview, add any records from the last update that are not in the current by checking the field [host name] this is what happens fine at the moment with the current code as you can see.


                      Now, I want to add one more thing. I want everything to happen as it does BUT during the load if the log files field [logged user] is empty then i want to use the last entry and not overwrite. the data should look like this


                      CSV file to load

                      Hostname   logged  user


                      345             username1         




                      Hostname logged user

                      123           username2



                      Final Result


                      Hostname    logged user

                      123              username2

                      345              username1