Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
hic
Former Employee
Former Employee

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

View solution in original post

12 Replies
flipside
Partner - Specialist II
Partner - Specialist II

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
Author

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

hic
Former Employee
Former Employee

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
Author

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
hic
Former Employee
Former Employee

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
Partner - Specialist II
Partner - Specialist II

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

hic
Former Employee
Former Employee

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

hic
Former Employee
Former Employee

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
Partner - Specialist II
Partner - Specialist II

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.