topic Re: Variables as columns issue in QlikView

Variables as columns issue

alec1982 — Wed, 07 Oct 2015 03:33:09 GMT

hi guys,

I have a script that load multiple files and use for functions to loop over each year and then another for to loop over each month to load a set of files..

trying to add the year var and month var as columns..

the year works fine but when I add the month var as column I loose all my data and no columns show on the data model..

any idea how to fix it?

ErrorMode = 0;

FOR vYear=2014 TO 2015

for each MonthName in 'January' ,'February','March','April','May','June', 'July', 'August', 'September', 'October', 'November', 'December'

Table1:

LOAD $(vYear) as ReportYear, //work fine and shows the right value

$(MonthName) as ReportMonth, cause errors and it cause the qvw to show no columns (Note: the errorMode=0 above)

FROM

[http://www.abc.com/~/media/Files/$(vYear)/data%20$(MonthName)%20$(vYear).xls]

(biff, embedded labels, header is 1 lines, table is [Info$]);

NEXT;

Re: Variables as columns issue

jagan — Wed, 07 Oct 2015 04:03:47 GMT

Hi,

Try like this

Table1:

LOAD $(vYear) as ReportYear, //work fine and shows the right value

'$(MonthName)' as ReportMonth, // Need to give single quotes for strings or dates otherwise you will get error

FROM

[http://www.abc.com/~/media/Files/$(vYear)/data%20$(MonthName)%20$(vYear).xls]

(biff, embedded labels, header is 1 lines, table is [Info$]);

Regards,

Jagan.

Re: Variables as columns issue

alec1982 — Wed, 07 Oct 2015 04:05:51 GMT

I just reached it at the same time you sent me your answer.. thanks so much!

Re: Variables as columns issue

tamilarasu — Wed, 07 Oct 2015 04:13:44 GMT

Hi Alec,

Just change the line as '$(MonthName)' as ReportMonth

Let me know the result.

Edit: Ignore this as Jagan Mohan also suggested the same (I was typing this and I didn't see Jagan's reply).