topic For Each logic - loading multi-company QVD's in QlikView

For Each logic - loading multi-company QVD's

Mon, 03 May 2010 00:52:46 GMT

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!

For Each logic - loading multi-company QVD's

Oleg_Troyansky — Mon, 03 May 2010 01:02:06 GMT

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,

For Each logic - loading multi-company QVD's

Mon, 03 May 2010 01:40:19 GMT

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?

For Each logic - loading multi-company QVD's

rwunderlich — Mon, 03 May 2010 06:03:27 GMT

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

For Each logic - loading multi-company QVD's

Mon, 03 May 2010 07:16:21 GMT

Absolutely fantastic!

Thanks very much Rob.