For Each logic - loading multi-company QVD's

[#1]

I have a situation where i have 5 company's with identical QVD files (i have simplified it down to one file)

These are stored in the structure

QVD\001\GLTrans.qvd

QVD\002\GLTrans.qvd

QVD\003\GLTrans.qvd

QVD\004\GLTrans.qvd

QVD\005\GLTrans.qvd

I don't want to have to concatentate each QVD for each company. The company list may expand and I want to create something that is easily maintainable. I'd rather write a script that loaded GLTrans.qvd for every company.

The key is, i need to also create primary key's by inserting the Company code (XXX) into the loaded table.

i.e.

FOR EACH CompanyCode

LOAD

INSERT CompanyCode as GLCompanyCode,

CompanyCode & GLAccountCode as GLCompanyAccountCode,

GLAccountCode,

GLTranDate,

GLTranAmount

FROM CompanyCode\GLTrans.qvd

Obviously this is pseduo-code. I'm not sure how to write the real code.

Could anyone please assist?

Thank you!

[Oleg_Troyansky]

Matt,

check out my "QlikView Explorer" in "Share QlikViews":

http://community.qlik.com/media/p/16.aspx

It's a bit old (developed on ver. 7.52), so some macros might not work, but in the script you can find an example of how to recursively read the folder structure and gather information about folders, files, etc...A simplified version of that script would do what I are trying to do.

cheers,

[#3]

Hi Oleg,

Thank you for your help, however at first look it's quite confusing.

For a starting point, is there something simpler I can look at?

[rwunderlich · Accepted solution]

Matt,

You can use the FOR EACH dirlist() syntax to process the company directories below the QVD node. Something like:

Directory QVD;
FOR EACH vDir IN dirlist('*')
LET vCompanyCode = subfield('$(vDir)', '\', -1);
GlTrans:
LOAD
'$(vCompanyCode)' as CompanyCode,
'$(vCompanyCode)' & GLAccountCode as GLCompanyAccountCode,
GLAccountCode,
GLTranDate,
GLTranAmount
FROM $(vDir)\GlTrans.qvd (qvd)
;
NEXT vDir

-Rob

[#5]

Absolutely fantastic!

Thanks very much Rob.