topic Re: How to read from a parameter file having multilines. in QlikView

How to read from a parameter file having multilines.

Wed, 12 Mar 2014 07:36:26 GMT

Hi,

I would like to accomplish the following scenario in QV.
I have a parameter file: parameter.txt. In this file, I have lines:
POS_1='AXP','ORB','CTIX','PTM','RQST','PLQL','MAR','CTP','FRN','TVE','MTE','JTGA','MOT'

POS_2='HCL','HCRG','CAN','ESR','HAA','EBCH','EBDE'

POS_3='MJDK','MJSE','NATW','COUT','EBAT','EBBE'

Now, I need to read the values assigned to each of the POS_* in my QV script and use them in a Query. For example:

-------------------------------

Temp_Table:
select POS_Name, POS_Location
from
test.pos_history
where pos_id in (Read value of POS_1 from parameter.txt) ////Need your help here

concatenate
select

POS_Name, POS_Location

from

test.pos_history

where pos_id in (Read value of POS_2 from parameter.txt) ////Need your help here

;

--------------------------------

Thank you.

Re: How to read from a parameter file having multilines.

alexandros17 — Wed, 12 Mar 2014 07:46:12 GMT

Here is your code for a single file

AAA:

load * inline [

pos_name

"POS_1='AXP','ORB','CTIX','PTM','RQST','PLQL','MAR','CTP','FRN','TVE','MTE','JTGA','MOT'"

"POS_2='HCL','HCRG','CAN','ESR','HAA','EBCH','EBDE'"

"POS_3='MJDK','MJSE','NATW','COUT','EBAT','EBBE'"

];

BBB:

NoConcatenate

LOAD Subfield(Mid(pos_name, Index(pos_name,'=')+1),',') as str Resident AAA;

DROP Table AAA;

Re: How to read from a parameter file having multilines.

Wed, 12 Mar 2014 08:04:37 GMT

Hi. Thanks for your reply.

But it seems, I should not use Inline Table in my script. The reason for this is not to touch the code in future, whenever I need to add/delete a POS id. Only place to change is the parameter file.

The second point of using Concatenate in my script is, data for each POS is so huge that if I keep all of them together, it results in DB time out error. Hence, I decided to divide it multiple SQL and merge them using Concatenate.

Re: How to read from a parameter file having multilines.

alexandros17 — Wed, 12 Mar 2014 08:30:38 GMT

I used the inline only to load data for my example, you may load data from other sources obviously ...

If you use the code I send you, and fix it for your file, then you have only to replicate it and concatenate each other

Re: How to read from a parameter file having multilines.

maxgro — Wed, 12 Mar 2014 08:34:25 GMT

see attachment

I started from Alessandro script (tkanks)

parameter.txt is your file

pos.txt is a test file (replace it with pos_history)

SCRIPT

Directory;

AAA:

LOAD @1 as pos_name

FROM

parameter.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

BBB:

load pos, replace(str, chr(39), '') as str;

NoConcatenate

LOAD

mid(pos_name,1,5) as pos,

Subfield(Mid(pos_name, Index(pos_name,'=')+1),',') as str Resident AAA;

DROP Table AAA;

CCC:

left keep (BBB)

LOAD @1 as pos,

@2 as str

FROM

pos.txt

(txt, codepage is 1252, no labels, delimiter is ',', msq);

DROP Table BBB;

Re: How to read from a parameter file having multilines.

Wed, 12 Mar 2014 11:09:42 GMT

Got your point. I tried doing that, but stuck at the second point of my question (if you see my question above...there is a place which I commented 'I need your help here').
At that point, how would I be able to call a QV table inside a SQL query? Means, How could I write something as:

Temp_Table:

select POS_Name, POS_Location

from

test.pos_history

where pos_id in (Load str from BBB) /// showing error here

////Need your help here: As AAA is a QV table while pos_history is my DB table.

Re: How to read from a parameter file having multilines.

Wed, 12 Mar 2014 11:13:31 GMT

Hello Massimo. Thanks for your inputs. But I find myself stuck at a point as how I could use the final values in the SQL statement...means:

Temp_Table:

select POS_Name, POS_Location

from

test.pos_history

where pos_id in (Load str from CCC ) /// showing error here

////Need your help here: As CCCis a QV table while pos_history is my DB table

Re: How to read from a parameter file having multilines.

maxgro — Wed, 12 Mar 2014 11:43:32 GMT

I think you have to loop on your QV table (in my example parameter.txt)

AAA:

LOAD

@1 as pos_name,

Right(@1, Len(@1)-6 ) as field

FROM parameter.txt

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LET rows=NoOfRows('AAA');

for i=0 to $(rows)-1

LET filter=peek('field', $(i));

trace $(i);

trace $(filter);

TABLE:

SQL

select POS_Name, POS_Location

from

test.pos_history

where pos_id in ('$(filter)'); ////Need your help here

NEXT;

Re: How to read from a parameter file having multilines.

Tue, 18 Mar 2014 10:57:41 GMT

Thank you!