Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Issues reloading script using a loop

Hi all,

I am trying to reload a csv using the below but nothing seems to happen and it quickly reverts to the Sheet Properties with no column attributes.

Can one of you kind people please take a look and tell me where I am going wrong?

My file name is called: "201106 TA_OG_Broker_Raw_Data_Report.csv" and it goes to "201204 TA_OG_Broker_Raw_Data_Report.csv"

set StartDate = '01/06/2011';
EndDate = addmonths(today(),-1);

let a = Interval(month(EndDate)-month(StartDate),'m');

for i = 0 to a;

let OmgeoFileName = Date(addmonths(StartDate,i),'YYYY')&Date(addmonths(StartDate,i),'MM')&' TA_OG_Broker_Raw_Data_Report.csv';

let OmgeoFileFullPath = ('');

Directory;
LOAD [Report Period Header],
  Date([Report Period Header],'MMM-YY') as [TA Time Month+Year],
  Year([Report Period Header]) as Year,
   Month([Report Period Header]) as Month,
     [Current Status],
     [Buy / Sell],
     [Security Code],
     [Number of Shares],
     [Trading Currency Code],
     [Number of Allocations],
     [Settlement Date],
     [Trade Date and Time],
     [SDC Indicator],
     [SDA Indicator],
     [Counterparty Institution Acronym]
FROM
$(OmgeoFileFullPath)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT

Many thanks in advance,

Matt

1 Solution

Accepted Solutions
Henric_Cronström
Not applicable

Re: Issues reloading script using a loop

Wildcards in file names work fine as long as you have a single Load, e.g.

Load ... from [*.csv] ;

But as soon as you put a preceding load in front of it, then you run into problems:

Load ...;

Load ... from [*.csv] ;

The second Load is piped into the first, i.e. the first Load becomes a transformation and filter layer. I use this construction a lot, because then I can make a step-wise transformation of data. But I cannot use wild cards in the file name...

HIC

12 Replies
flipside
Not applicable

Issues reloading script using a loop

Hi Matt,

Rather than looping, can you use a wildcard in the filename? Such as ...

Directory;

LOAD [Report Period Header],

...

     [Counterparty Institution Acronym]

FROM

[* TA_OG_Broker_Raw_Data_Report.csv]

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

flipside

Not applicable

Re: Issues reloading script using a loop

Hi flipside, thanks for your response.

Do I just need to change this:

FROM

$(OmgeoFileFullPath)

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

NEXT

to this:

FROM

[* TA_OG_Broker_Raw_Data_Report.csv]

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

If so, that doesn't seem to be working.

Many thanks,

Matt

Henric_Cronström
Not applicable

Issues reloading script using a loop

You can use wildcards in the file name, but I do not recommend it. It causes problems as soon as you use preceding loads.

Instead I think you should fix your loop. Your "Let OmgeoFileFullPath = ..." is incorrect. Use this instead:

let OmgeoFileFullPath = 'O:\MI\2012\OMGEO Downloads - NEW\' & $(OmgeoFileName) ;

Also, change your Load statement from "FROM $(OmgeoFileFullPath) (txt..." to "FROM [$(OmgeoFileFullPath)] (txt..."

HIC

Not applicable

Re: Issues reloading script using a loop

Thank you MIC but this doesn't work either.  I've copied the full script again in case I am missing anything..

set StartDate = '01/06/2011';
EndDate = addmonths(today(),-1);

let a = Interval(month(EndDate)-month(StartDate),'m');

for i = 0 to a;

let OmgeoFileName = Date(addmonths(StartDate,i),'YYYY')&Date(addmonths(StartDate,i),'MM')&' TA_OG_Broker_Raw_Data_Report.csv';

let OmgeoFileFullPath = 'O:\MI\2012\OMGEO Downloads - NEW\' & $(OmgeoFileName);


Directory;
LOAD [Report Period Header],
  Date([Report Period Header],'MMM-YY') as [TA Time Month+Year],
  Year([Report Period Header]) as Year,
   Month([Report Period Header]) as Month,
     [Current Status],
     [Buy / Sell],
     [Security Code],
     [Number of Shares],
     [Trading Currency Code],
     [Number of Allocations],
     [Settlement Date],
     [Trade Date and Time],
     [SDC Indicator],
     [SDA Indicator],
     [Counterparty Institution Acronym]
FROM
[$(OmgeoFileFullPath)]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT

Many thanks,
Matt
Henric_Cronström
Not applicable

Re: Issues reloading script using a loop

Sorry, I missed a couple of single quotes...

One of the following should work:

let OmgeoFileFullPath = 'O:\MI\2012\OMGEO Downloads - NEW\' & '$(OmgeoFileName)' ;

set OmgeoFileFullPath = O:\MI\2012\OMGEO Downloads - NEW\$(OmgeoFileName) ;

If not, I suggest you run the script in the debugger and check that you really have the right file names.

HIC

flipside
Not applicable

Issues reloading script using a loop

Hi Henric,

Thanks for the info, I wasn't aware of any issues with wildcards in loads - I haven't had any myself. What sort of problems does it cause?

Matt >  I was also going to add that I don't think the loop logic works, because your start value is Month(01/06/2011) = 6 and your end value will be 4.  Therefore you are trying to loop from 6 to 4.  Also, is Interval the right function, I thought this was just for time intervals (up to 'day' value)?

If it's just a case of avoiding Preceding Loads, then the wildcard load should work (you don't need the loop) but with a where clause added ...

Directory;

LOAD [Report Period Header],

...

     [Counterparty Institution Acronym]

FROM

[* TA_OG_Broker_Raw_Data_Report.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where left(FileName(),6)>='$(StartDate)'

and left(FileName(),6)<'$(EndDate)';

The variable StartDate would need to be generated to equate to 201106 and EndDate to 201205 (CurrentMonth).

flipside

Henric_Cronström
Not applicable

Re: Issues reloading script using a loop

I just saw something else which might cause a problem:

let a = Interval(month(EndDate)-month(StartDate),'m');

The Interval function takes a timestamp as parameter and you have months. That you format as minutes.

HIC

Henric_Cronström
Not applicable

Re: Issues reloading script using a loop

Wildcards in file names work fine as long as you have a single Load, e.g.

Load ... from [*.csv] ;

But as soon as you put a preceding load in front of it, then you run into problems:

Load ...;

Load ... from [*.csv] ;

The second Load is piped into the first, i.e. the first Load becomes a transformation and filter layer. I use this construction a lot, because then I can make a step-wise transformation of data. But I cannot use wild cards in the file name...

HIC

flipside
Not applicable

Re: Issues reloading script using a loop

Matt,

Another tip if you don't want to use the debugger, just add a msgbox line to check your variables during the load (but be careful using this in long loops - you might have a lot of key presses!!)

eg Add this after your 'Let a = ...' line

x = msgbox($(a));

I got a value of -2880 in my test!!

flipside

EDIT: Also remember to remove or comment it out when testing finished.