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

    Update but only if source field is not null

    Allan Wilesmith

      HI,

       

      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

      1              

      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:

           

          TempTable:

          LOAD ASSET as ASSET_CHECK

          FROM CSV File

          Where Len(Trim(USER)) = 0;

           

          FACTTable:

          LOAD *

          FROM CSV File

          Where Len(Trim(USER)) > 0;

           

          Concatenate(FactTable)

          LOAD *

          FROM INCREMENTAL FILE

          Where Exists(ASSET_CHECK, ASSET);

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

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

              [incremental]:

              LOAD [IP Address],

                  [MAC Address],

                  [Serial Number],

                  [Logged User],

                  [Response Time],

                  [Host Name],

                  [Last Scan],

                  [Last Seen],

                  [Installed Software],

                  [CSName],

                  [USB Printer]

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

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

               

               

              Concatenate (incremental)

              load*

              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
                      Allan Wilesmith


                      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

                      123 

                      345             username1         

                       

                      Qlikview    

                       

                      Hostname logged user

                      123           username2

                       

                       

                      Final Result

                       

                      Hostname    logged user

                      123              username2

                      345              username1