topic Re: How to pass the path as a parameter in sub routine? in QlikView

How to pass the path as a parameter in sub routine?

d_koti24 — Tue, 02 Jun 2015 10:32:21 GMT

Hi,

I have 4 qvds,That 4 Qvds contains the same fields,i was created one sub routine,how to pass qvd path as parameter.

sub routine:

sub sample(z_path)

load

from

z_path

(qvd);

call sample("f:\qvds\qvd1.qvd")

while running this app ,I did nt get any error.but It did n't load the data.

How to pass the path as a parameter in sub routine?

regards,

Re: How to pass the path as a parameter in sub routine?

arulsettu — Tue, 02 Jun 2015 10:42:39 GMT

hi try like this

SET QvdPath = f:\qvds;

sub sample:

load

from $(QvdPath)\sub sample.qvd;

Re: How to pass the path as a parameter in sub routine?

maxgro — Tue, 02 Jun 2015 10:46:40 GMT

changed something, in bold

sub sample(z_path)

trace z_path='$(z_path)';

load

from $(z_path) (qvd);

ENDSUB;

call sample('f:\qvds\qvd1.qvd');

Re: How to pass the path as a parameter in sub routine?

Gabriel — Tue, 02 Jun 2015 10:48:29 GMT

Hi,

I would suggest having you path in variable

LET vPath = '..\..\FileSource';

Then

SUB Sample(TableName,vPath)

STORE '$(TableName)' INTO $(vPth).QVD(QVD);

ENDSUB;

Call Sample('TableName',vPath);

Re: How to pass the path as a parameter in sub routine?

d_koti24 — Tue, 02 Jun 2015 14:31:59 GMT

Hi Massimo,

Thanks for your quick reply...! it's working fine.

after that I want to split that qvd into monthly wise qvd's based on date field and store that qvd's into req path.hw to achive this.?that req path also I want to pass as a paramerter.

Re: How to pass the path as a parameter in sub routine?

maxgro — Tue, 02 Jun 2015 16:09:01 GMT

// load and store 1 month

SUB LoadAndStore (vFile, vYearMonth)

trace vFile='$(vFile)';

sourcebymonth:

load *

from $(vFile) (qvd)

where Year(d) = left('$(vYearMonth)',4) and Month(d) = Right('$(vYearMonth)',2);

let vFile2=replace('$(vFile)', '.qvd', '');

TRACE vFile2=$(vFile2);

store sourcebymonth into $(vFile2)_$(vYearMonth).qvd (qvd);

drop Table sourcebymonth;

ENDSUB;

// test data: qvd with 2 year

LET myQvdFile='C:\Users\mgrossi\Downloads\source.qvd';

source:

LOAD

date(makedate(2014) + floor(rand()*730)) as d,

floor(rand() * 100) as val

AutoGenerate 1000;

store source into $(myQvdFile) (qvd);

DROP Table source;

// min and max year month

tmp: load

date(min(MonthStart(d)), 'YYYYMM') as MinMonth,

date(max(MonthStart(d)), 'YYYYMM') as MaxMonth

from $(myQvdFile) (qvd);

let vMin=peek('MinMonth');

let vMax=peek('MaxMonth');

trace vMin=$(vMin) vMax=$(vMax);

DROP Table tmp;

// loop on year month

for ym=$(vMin) to $(vMax)

if Right(ym,2)>0 and Right(ym,2)<=12 then

trace ym=$(ym);

CALL LoadAndStore('$(myQvdFile)', '$(ym)');

ENDIF;

NEXT ym;

Re: How to pass the path as a parameter in sub routine?

d_koti24 — Wed, 03 Jun 2015 08:12:25 GMT

Hi Massimo,

This is my sample code ,i am getting error at store place.it's working fine upto store command.

sub sample(z_path,z_path1, s_loc)

trace z_path='$(z_path)';

trace z_path1='$(z_path1)';

trace s_loc='$(s_loc)';

FACT:

LOAD a,

PERIOD

FROM

$(z_path) (qvd);

Concatenate

LOAD LOAD a,

PERIOD

FROM

$(z_path1) (qvd);

Dates:

LOAD DISTINCT

left(DATE(PERIOD,'MM/DD/YYYY'),2) AS MONTH, YEAR(DATE(PERIOD)) AS YEAR

RESIDENT FACT;

LET vNoOfMthsYrs = NoOfRows('Dates');

set qv_name='sample';

trace qv_name='$(qv_name)';

FOR i = 0 TO $(vNoOfMthsYrs) - 1

LET vMonth = PEEK('MONTH', $(i), 'Dates');

LET vYear = PEEK('YEAR', $(i), 'Dates');

MONTHLY:

LOAD *,'' as tmp RESIDENT FACT

WHERE left(DATE(PERIOD,'MM/DD/YYYY'),2) = '$(vMonth)' AND YEAR(DATE(PERIOD)) = '$(vYear)';

STORE MONTHLY into [$(s_loc)\$(qv_name)$.(vYear)_$(vMonth).qvd](qvd);

drop field tmp;

drop table MONTHLY;

Drop tables FACT,Dates;

ENDSUB;

call sample('d:\sample1.qvd','d:\sample2.qvd','d:\monthlyqvds');

regards,