topic Concatenate same structrured QVDs from different folders in QlikView

Concatenate same structrured QVDs from different folders

dmohanty — Thu, 24 Jan 2013 17:59:30 GMT

I have QVDs with same names, but extracted from different databases. Those QVDs have same table structures. For example I have A.qvd and B.qvd and are stored in X and Y folders each (X is having A & B from one database and Y is having A & B from other database). Similary I have multiple folders and more than hunderd QVDs in each. I need to concatenate all the same QVDs from each folders in a single go using a loop.

I am using the below way. Please advice a better way if anyone have.

$(Include=c:\FolderNames.txt);
$(Include=c:\qvdpath.txt);
$(Include=c:\tablenames.txt);

let vtable=NoOfRows('Table_names');

for i=0 to $(vtable)-1

let vtablename=Peek('Table_names',$(i),'QVD_names');

let n=NoOfRows('Plant_name');

for j=0 to $(n)-1
let vplantname=Peek('Plant_name',$(j),'test');

$(vtablename):
LOAD * FROM
$(vqvdpath)\$(vplantname)\$(vtablename).qvd(qvd);
next
store $(vtablename) into C:\Consolidated QVDs\$(vtablename).qvd(qvd);
drop table $(vtablename);

Re: Concatenate same structrured QVDs from different folders

dmohanty — Mon, 28 Jan 2013 19:42:01 GMT

Hi Friends,

I revised the code a bit to concatenate the QVDs from two different folders. But the problem is that, when the QVDs from 2nd folder are coming, they are replacing the QVDs generated from 1st folder. My aim is to concatenate the QVDs from both the folders. Please suggest. Do I need to mention CONCATENATE somewhere?

Below is my code:

set errormode = 0;

let qvdpath = 'C:\Users\DICKY\Desktop';

for each vPlant in '$(qvdpath)\MAP\' , '$(qvdpath)\HPG\'

for each qvd_file in FileList('$(vPlant)*')

let QVDName = subfield('$(qvd_file)','\',6);

$(QVDName):

load

FROM

$(vPlant)$(QVDName)

(qvd);

store $(QVDName) into C:\Users\DICKY\Desktop\Test QVDs\$(QVDName);

drop Tables $(QVDName);

EXIT Script;

Re: Concatenate same structrured QVDs from different folders

Anonymous — Mon, 28 Jan 2013 20:01:57 GMT

I may be over simplifying here but you could loop through the first folder and load the QVDs using the $(QVDName) as the table name Then loop through the second folder using the concatenate with $(QVDName). Try something similar to what is below.

let qvdpath = 'C:\Users\DICKY\Desktop';
for each qvd_file in FileList('$(qvdpath)\MAP\')
     let QVDName = subfield('$(qvd_file)','\',6);
     $(QVDName):
          load 
          *
     FROM
     $(qvdpath)\MAP\$(QVDName)
     (qvd);
next
for each qvd_file in FileList('$(qvdpath)\HPG\')
let QVDName = subfield('$(qvd_file)','\',6);
     Concatenate($(QVDName)):
          load 
          *
     FROM
     $(qvdpath)\HPG\$(QVDName)
     (qvd);
store $(QVDName) into C:\Users\DICKY\Desktop\Test QVDs\$(QVDName);
drop Tables $(QVDName);
next

Re: Concatenate same structrured QVDs from different folders

dmohanty — Mon, 28 Jan 2013 21:11:35 GMT

Hi Kevin,

Thank you very much for the solution. Though this piece of code becomes a bit lengthier, but it works perfectly. I was just wondering if we can write 'LOAD * FROM ' just once using every possible loops, it would be better. If you strike upon any such ideas, please share too.

Appreciate your quick turnaround.

Regards.

Re: Concatenate same structrured QVDs from different folders

Anonymous — Mon, 28 Jan 2013 21:43:58 GMT

Yeah, my solution probably isn't the most eloquent but I wanted to at least give you something that would accomplish what you set out to do. I'm not sure if this is going to work since I've never had to do something like that but I thought this was at least worth a shot. You may need some single quotes around the if statements, I'm not sure about the formatting for what I've provided.

let qvdpath = 'C:\Users\DICKY\Desktop';
for each qvd_file in FileList('$(qvdpath)\MAP\')
for each vPlant in  '$(qvdpath)\MAP\' , '$(qvdpath)\HPG\'
     let QVDName = subfield('$(qvd_file)','\',6);
     If($(vPlant)='$(qvdpath)\MAP\','$(QVDName)','Concatenate($(QVDName))'):
          load 
          *
     FROM
     If($(vPlant)='$(qvdpath)\MAP\','$(qvdpath)\MAP\$(QVDName)','$(qvdpath)\HPG\$(QVDName)')
     (qvd);
next
store $(QVDName) into C:\Users\DICKY\Desktop\Test QVDs\$(QVDName);
drop Tables $(QVDName);
Next

Re: Concatenate same structrured QVDs from different folders

rwunderlich — Mon, 28 Jan 2013 23:04:30 GMT

I'm not seeing why you need to extract the qvdname. qvd_file inside the FileList loop should have the complete path to the qvd. So it looks to me like the somple:

LOAD * FROM [$(qvd_file)] (qvd);

should work.

-Rob

Re: Concatenate same structrured QVDs from different folders

dmohanty — Tue, 29 Jan 2013 04:02:48 GMT

Thanks Kevin and Rob for trying your hands on my query. But somehow the 'IF' condition is showing some syntax error.However I will take it forward to simplify, if possible.

Can you please help me out with the challenge provided in the below thread? If possible reply in the respective thread itself.

http://community.qlik.com/message/303936#303936

Regards.

Re: Concatenate same structrured QVDs from different folders

dmohanty — Tue, 29 Jan 2013 09:07:22 GMT

Hi,

Using this process the QVD gets concatenated very well. But the problem is, if there are some extra QVDs in one folder that are not in other folder, those QVDs are not being stored/dropped. "Table not found" error comes. Can we use some IF ELSE statement? Unluckily IF ELSE syntax not working. Can you please help?

Re: Concatenate same structrured QVDs from different folders

Anonymous — Tue, 29 Jan 2013 13:26:25 GMT

If you're going to use the If...ElseIF...Else...EndIf syntax, you're going to likely end up repeating your Load * From statement twice. If that's the case, you might as well keep things simple and use the first answer.