topic Issues reloading script using a loop in QlikView

Issues reloading script using a loop

Thu, 31 May 2012 07:30:03 GMT

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

Issues reloading script using a loop

flipside — Thu, 31 May 2012 08:03:21 GMT

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

Re: Issues reloading script using a loop

Thu, 31 May 2012 08:14:06 GMT

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

Issues reloading script using a loop

hic — Thu, 31 May 2012 08:16:40 GMT

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

Re: Issues reloading script using a loop

Thu, 31 May 2012 08:28:16 GMT

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

Re: Issues reloading script using a loop

hic — Thu, 31 May 2012 08:33:57 GMT

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

Issues reloading script using a loop

flipside — Thu, 31 May 2012 08:38:46 GMT

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

Re: Issues reloading script using a loop

hic — Thu, 31 May 2012 08:40:12 GMT

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

Re: Issues reloading script using a loop

hic — Thu, 31 May 2012 08:44:57 GMT

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

Re: Issues reloading script using a loop

flipside — Thu, 31 May 2012 08:47:09 GMT

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.

Re: Issues reloading script using a loop

Thu, 31 May 2012 12:54:28 GMT

Hi Guys,

Many thanks for both your feedback however I am still getting an issue. I have run it through the debugger and it looks like it's s file path issue:

OmgeoFileFullPath <NULL>
OmgeoFileName <NULL>

However, I know the filepath is exactly right which is why I am even more baffled.

Just to put this into context, I get issues reloading using multiple files (I mean importing each file one by one). So I just wanted to get one load to prevent this problem.

Do you know of any alternitives (btw I am new to all this and only just learning the QV code)

Many thanks again.

Matt

Re: Issues reloading script using a loop

hic — Thu, 31 May 2012 13:08:32 GMT

Yup, I see another problem: You StartDate is a string and not a number, and then your month(StartDate) will fail.

Never mind. Try this instead. It's simpler:

for each vFileName in FileList('O:\MI\2012\OMGEO Downloads - NEW\*.csv')

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,

'$(vFileName)' as FileName,

[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 [$(vFileName)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Then you can extract the date from the field FileName using string functions (subfield, left, right, etc.)

HIC

Re: Issues reloading script using a loop

Thu, 31 May 2012 13:27:37 GMT

Many thanks Henric for replying with the below message

Yup, I see another problem: You StartDate is a string and not a number, and then your month(StartDate) will fail.
Never mind. Try this instead. It's simpler:
for each vFileName in FileList('O:\MI\2012\OMGEO Downloads - NEW\*.csv') 
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,
             '$(vFileName)' as FileName,
     [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 [$(vFileName)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
NEXT
Then you can extract the date from the field FileName using string functions (subfield, left, right, etc.)