2 Replies Latest reply: Jul 25, 2018 9:22 AM by Bala Bhaskar RSS

    Save results of formulas in Qlik

    Pascal Dijkshoorn

      Hello,

       

      I am working with a dataset that overwrites data. This means everyday when I reload the data, i get different results and I can't see the results of the day before anymore. So the results of my formula's only show the current situation. Is it possible to save these results, so i can create a timeline of results.

       

      For example, I have a formula that calculates the occupancy. Is it possible to create a formula that saves the occupancy daily, so i get a timeline with the occupancy per day?

        • Re: Save results of formulas in Qlik
          Gabor Tarnoczai

          Hi Pascal,

           

          You can store these information in file.

          You can create a file in load script, read it, append a new line and store the new file.

           

          Help link to store command:

          https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Store.htm

           

          G.

          • Re: Save results of formulas in Qlik
            Bala Bhaskar

            May be you can save the data into QVD's on daily basis:

             

            ABC:

            LOAD *;

            SQL

            Select * from ABC where Date = 'DD/MM/YYYY';

             

            LET vDay = Date(Today(),'DD/MM/YYYY'');


            STORE ABC into D:\$(vDay).qvd;


            ********************* After storing data into QVDs, call them into application using below script ********************


            for each vFile in FileList('.\Data\*.qvd')

                 let vFileName = mid(vFile, index(vFile, '\', -1) + 1, 99);

             

                 QvdFields:

                 LOAD

                      1                      as FieldCount,

                      '$(vFileName)'         as [File Name],

                      FieldName              as [Field Name],

                      BitOffset              as [Bit Offset],

                      BitWidth               as [Bit Width],

                      Bias                   as [Bias],

                      NoOfSymbols            as [No Of Symbols],

                      [NumberFormat/Type]    as Type,

                      [NumberFormat/nDec]    as Dec,

                      [NumberFormat/UseThou] as UseThou

                 FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

            next

             

            if you also want to add in some header information, such as number of rows, you can add another statement within the loop to pull in those values:

             

                 QvdTableHeader:

                 LOAD

                      1                      as QVDCount,

                      '$(vFileName)'         as [File Name],

                      QvBuildNo              as [QV Build No],

                      CreatorDoc             as [QVD Creator],

                      CreateUtcTime          as [Time Created],

                      SourceFileSize         as [Source File Size],

                      TableName              as [Table Name],

                      RecordByteSize         as [Record Byte Size],

                      NoOfRecords            as [Number Of Records]

                 FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader]);