topic Re: creating qvd daily with variable values in QlikView

creating qvd daily with variable values

sayadutt — Sat, 16 Nov 2024 02:41:28 GMT

Can someone please help me here. I am very new to qlikview.

Sample Data I have: (reading from excel)

Month, Item, Status

Jan, A, In Progress

Jan, B, Completed

Jan, C, Completed

Feb, D, In Progress

Feb, E, Completed

Feb, F, In Progress

In Set Expression, I am calculating Pass and Fail based on below Logic:

For Jan, Pass = 2 (sum of Completed ones), Fail = 1 (sum of In Progress)

For Feb, Pass = 1 (only 1 Completed), Fail = 2(sum of In Progress)

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

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

These 2 variables are set using Settings->Variable Overview

Requirement:

1) Each day, data is being updated in the above table.

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

Note: Don't want to create new qvd's everyday as the count of files will be more over the months. Need something to append the existing qvd with each day's data.

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

Can you please help.

Re: creating qvd daily with variable values

NZFei — Wed, 21 Aug 2019 04:44:06 GMT

Are you sure you want to store item as well?

With $(vPass) and $(vFail), how can you put item in the same line?

Maybe you need two QVD files?

One with the following data:

Month, $(vPass), $(vFail), Store Date

And the other one is your original data, plus store date.

Month, Item, Status, Store Date.

Fei

Re: creating qvd daily with variable values

sayadutt — Wed, 21 Aug 2019 06:23:12 GMT

If required, I can have 2 qvds. Can you please suggest how to achieve this.

Re: creating qvd daily with variable values

jmartineze — Wed, 21 Aug 2019 12:30:55 GMT

Hi, why not update the QV and load the old qvd, concatenate two tables and store it?

Re: creating qvd daily with variable values

NZFei — Wed, 21 Aug 2019 21:11:11 GMT

If you want item, then for vPass and vFail, they will be either 1 or 0 in each line. Is this all you want?

Month	Item	vPass	vFail	Date
Jan	A	0	1	21/08/2019
Jan	B	1	0	21/08/2019
Jan	C	1	0	21/08/2019
Feb	D	0	1	21/08/2019
Feb	E	1	0	21/08/2019
Feb	F	0	1	21/08/2019

If so, they will have the same lines as the original data. saving it to QVD file will be the same size as saving the following data:

Month	Item	Status	Date
Jan	A	In Progress	21/08/2019
Jan	B	Completed	21/08/2019
Jan	C	Completed	21/08/2019
Feb	D	In Progress	21/08/2019
Feb	E	Completed	21/08/2019
Feb	F	In Progress	21/08/2019

For each way, you can do this:

1) First time load the following script to export data to a QVD file.

set vQVD = D:\WhateverFoler;

Temp: //Act as the data source
load * inline[
Month, Item, Status
Jan, A, In Progress
Jan, B, Completed
Jan, C, Completed
Feb, D, In Progress
Feb, E, Completed
Feb, F, In Progress
];

DataTable:
Load
Month,
Item,
If(Status='In Progress',1,0) as Fail,
If(Status='Completed',1,0) as Pass,
today() as Date
Resident Temp;
drop table Temp;

store DataTable into [$(vQVD)\WhateverFileName.qvd] (qvd);

Then everyday, do the same, concatenate the existing file and then save it.

Temp: //Act as the data source
load * inline[
Month, Item, Status
Jan, A, In Progress
Jan, B, Completed
Jan, C, Completed
Feb, D, In Progress
Feb, E, Completed
Feb, F, In Progress
];

DataTable:
Load
Month,
Item,
If(Status='In Progress',1,0) as Fail,
If(Status='Completed',1,0) as Pass,
today() as Date
Resident Temp;
drop table Temp;

Concatenate
load
*
from [$(vQVD)\WhateverFileName.qvd] (qvd);

store DataTable into [$(vQVD)\WhateverFileName.qvd] (qvd);

I have attached the sample file.