topic 'qvd' file generator depending on a query result in QlikView

'qvd' file generator depending on a query result

Thu, 04 Aug 2011 13:05:16 GMT

Hello. I want to create a loader script which stores qvd files with postfixes depending on a query result. (For example

if query result is 1,2,5,7 then file names are: file1.qvd, file2.qvd, file5.qvd, file7.qvd)

What should I use for iteration to avoid files for non-existing numbers in query ("gaps in the row")?

I would be grateful for working examples.

'qvd' file generator depending on a query result

Anonymous — Thu, 04 Aug 2011 15:35:46 GMT

Hi guy,

here is an example that I create to show you what you could do. You can use this script like this in a new document.

my script :

// First you declare your table

table1:
load * inline [
val1, dep
12 , aa
123, aa
223, bb
233, cc
];

// you make a loop for each row of your table
for i = 1 to NoOfRows('table1')
       // you can get the name of the field that you want
   let vField = FieldName(1,'table1');
        // you can get the value of a field like this
   LET vfieldvalue = FieldValue('$(vField)',$(i));
        //you can store like this with a concatenation of the value of your data and a generic string
   store * from table1 into test & $(vfieldvalue).qvd ;

next i

This example wil create 4 qvd named like this :

test12.qvd, test123.qvd, test223.qvd, test233.qvd

Hope that it will help you.

'qvd' file generator depending on a query result

Thu, 04 Aug 2011 15:39:40 GMT

Hi,

this is a littlee Script based on a german Northwind DB:

ODBC CONNECT TO Nordwind;

Customer:
SQL SELECT Firma as Company, //field is not null
    KundenCode,
    Land,
    Ort,
    Region
FROM nordwind.kunden;

Counter:
Load num(count(Company)) as Number
resident Customer;

Let varNumberEnd = num(Peek('Number',-1,'Counter'));

for a=0 to $(varNumberEnd)-1

Let varCompany = Peek('Company',$(a),'Customer');

[StoreCust$(varCompany)]:
noconcatenate load *
resident Customer
where '$(varCompany)'=Company;

store [StoreCust$(varCompany)] into 'StoreCust'&$(varCompany).qvd;
drop table [StoreCust$(varCompany)];

Hope that helps.

Regards

Sabine

'qvd' file generator depending on a query result

Fri, 05 Aug 2011 07:30:29 GMT

Thank You!. In fact I found an answer from other question ( http://community.qlik.com/message/7705#7705 ), but yours is the closest to the solution that I implemented, so I count it for correct answer.