topic Re: Dynamic Concatenation of QVD in QlikView

Dynamic Concatenation of QVD

balrammahato204 — Sat, 16 Nov 2024 02:29:41 GMT

Hi All,

Please help to achieve the below scenario. I have below code to Concatenate the Incremental QVD with the current Year and starting of the new year need to Archive the QVD on HISTORICAL QVD. Right now this i am doing Manually once new Year is came then i am changing the QVD Name to the Current Year.

eg:-

TABLE_A_2019.qvd (qvd) is Current QVD

On the 1st Jan 2020 i am going to change this name into TABLE_A_2020.qvd (qvd) and respectively i am going to change the QVD name inside the Table B to archive the 2019 data to the Historical QVD. Can i change the QVD Name dynamically and that QVD is going to be Archive on Table B once new Year will come.

Note :- I cannot do concatenation on Tabke_B with Load * from '$(QvdPath)'\TABLE_A_*.qvd (qvd); because in my 2018 and 2019 data not having the same fields. Few fields are available on 2018 which are not available on 2019. So i have to concatenate with specific name of the QVD only.

TABLE_A_2020.qvd (qvd)-->This I can create using the MAX Year variable from the Calendar . Only concern is how can i append the TABLE_A_2019.qvd (qvd) into Historical.

Year --> i can check with master calendar.

Please let me know if required more details.

TABLE_A:

Load * from '$(QvdPath)'\TABLE_A_2019.qvd (qvd);

Concatenate(TABLE_A)

Load * from '$(QvdPath)'\TABLE_A_CURRENT_QTR_INC_LOAD_*.qvd (qvd);

STORE TABLE_A into '$(QvdPath)'\TABLE_A_2019.qvd;

Drop Table TABLE_A;

TABLE_B:
Load * from '$(QvdPath)'\TABLE_A_2019.qvd (qvd);

Concatenate(TABLE_B)

Load * from '$(QvdPath)'\TABLE_A_2018.qvd (qvd);

STORE TABLE_B into '$(QvdPath)'\HISTORICAL.qvd;
Drop Table TABLE_B;

Regards

Balram

Re: Dynamic Concatenation of QVD

DavidM — Fri, 13 Sep 2019 08:24:13 GMT

I suggest you store the year in variable, something like this:

Let vYear = Year(Today())

Let vLastYear = Year(Today())-1

And then simple use the variables in the load script:

Load * from '$(QvdPath)'\TABLE_A_$(vYear).qvd (qvd);

Load * from '$(QvdPath)'\TABLE_A_$(vLastYear).qvd (qvd);

Re: Dynamic Concatenation of QVD

balrammahato204 — Fri, 13 Sep 2019 09:51:30 GMT

Thanks David For your response.

if i am applying the suggested code then i can store only two year data current and previous. But once we reach to the 2021 i am going to loose the 2018 data. Is any other solution. I cannot create one new variable for each new year because it's again a manual task.

Regards

Balram

Re: Dynamic Concatenation of QVD

asinha1991 — Fri, 13 Sep 2019 11:43:41 GMT

you can use for each

Set vConcatenate= ;

for each FileExtension in 'qvd'
for each FoundFile in filelist( QvdPath& '\TABLE_A_*.' & FileExtension)

TableA:

$(vConcatenate)

Load * from [$(FoundFile)] (qvd);

Set vConcatenate = Concatenate;

next FoundFile
next FileExtension

//check for minor syntax things

Re: Dynamic Concatenation of QVD

Brett_Bleess — Wed, 25 Sep 2019 13:18:17 GMT

Balram, did the other post help you get what you needed here, or are you still trying to come up with a workable solution? If the last post did work, be sure to use the Accept as Solution button to let other know that worked etc., and if you are still trying to figure things out leave an update as to where you are with things and perhaps a sample app may help here too, as these are somewhat difficult to guess what you are really doing and can impact folks being willing to offer up ideas.

The only things I have to offer are a Design Blog post and Help link just in case you have not checked those:

https://community.qlik.com/t5/Qlik-Design-Blog/Overview-of-Qlik-Incremental-Loading/ba-p/1466780

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/QVD_Incremental.htm

Not sure either of those is what you really need, but wanted to toss them out.

Regards,
Brett