topic Storing historical data in QlikView

Storing historical data

sayadutt — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have few fields which I display in my qvw file. Now I need to store these field values everyday and use it later to plot a trending graph.

For example: I have a variable $(vPass) and $(vFail). Everyday these values will be updated. I don't want to loose the previous days values and want to plot a trending graph based on no. of days.

Can you please guide me how I can store the values and later use it to plot values.

I am using excel data as my input.

Thanks

Re: Storing historical data

Anil_Babu_Samineni — Sat, 17 Aug 2019 14:07:38 GMT

Can't you create variables like $(vPass_Constant) and $(vFail_Constant) and for dynamic like $(vPass) and $(vFail)

Re: Storing historical data

jonathandienst — Sat, 17 Aug 2019 14:52:43 GMT

After you have calculated the pass/fail values and populated vPass and vFail, append into a qvd file like this:

History: LOAD $(vPass) as Pass, $(vFail) as Fail, Now() as Timestamp Autogenerate 1; If Alt(QvdCreateTime('History.qvd'), 0) > 0 Then Concatenate(History) LOAD Pass, Fail, Timestamp FROM History.qvd (qvd); End If Store History into History.qvd (qvd);

Re: Storing historical data

sayadutt — Mon, 19 Aug 2019 14:45:23 GMT

Hi,

Above script is giving me below error.

Syntax error, missing/misplaced FROM:
History:
LOAD as Pass,
as Fail,
Now() as Timestamp
Autogenerate 1
History:
LOAD as Pass,
as Fail,
Now() as Timestamp
Autogenerate 1

Re: Storing historical data

jonathandienst — Tue, 20 Aug 2019 05:25:26 GMT

You need to populate the variables vPass and vFail before using this script fragment.

Re: Storing historical data

sayadutt — Tue, 20 Aug 2019 22:51:44 GMT

Hello Jontydkpi,

Can you please give me a sample example. I am very new to qlikview.

Sample Data I have:

Month, Item, Status

Jan, A, In Progress

Jan, B, Completed

Jan, C, Completed

Feb, D, In Progress

Feb, E, Completed

Feb, F, In Progress

So now, for Jan, Pass = 2 (sum of Completed ones), Fail = 1 (sum of In Progress)

For Feb, Pass = 1 (only 1 Completed), Fail = 2

$(vPass) = Count({<QA Status = {'Completed'}>} Item)

$(vFail) = Count({<QA Status = {'In Progress'}>} Item)

These 2 variables are set using Settings->Variable Overview

Requirement:

1) Need to create a qvd every night which will store Month, $(vPass), $(vFail), Item, Date

2) later use this qvd to create a trend graph (monthwise) .

Can you please help.