topic Re: Save results of formulas in Qlik in App Development

Save results of formulas in Qlik

pascaldijkshoor — Wed, 25 Jul 2018 09:36:14 GMT

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

undergrinder — Wed, 25 Jul 2018 09:45:43 GMT

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

Re: Save results of formulas in Qlik

BalaBhaskar_Qlik — Wed, 25 Jul 2018 13:22:37 GMT

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]);

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]);