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

Variations in names on a batch load

Hi,

Im not sure of the terminology for this type of load but I have hundreds of files in a folder that are all formatted the same with a defined naming function that indicates the week they relate to. I do a load where I replace the file name with a * and it loads all of the files in that folder... possibly a wild card??

e.g. FROM (ansi, txt, delimiter is '\t', embedded labels, msq);

The problem I have come across is that one of the columns was previously called "Sales" in older reports and it has changed to "Sales ($)" in the last few weeks.

If it was just an internal report I would make sure they were uniform but as they are reports downloaded from an external source I need to find a way around it.

Below is the load, is there a way to tell it to load Sales if the file has Sales as the heading or Sales ($) if thats the heading.

LOAD State,
[Store] as Location,
Product,
Barcode as Barcode#,
Sales
FROM (ansi, txt, delimiter is '\t', embedded labels, msq);

I tried adding a second line under Sales as "Sales ($) as Sales" but that didnt work.

Any ideas?

Thanks

David

1 Solution

Accepted Solutions
Not applicable
Author

how about something like

if(isnull(Sales),[Sales ($)], Sales) as Sales

View solution in original post

4 Replies
Not applicable
Author

how about something like

if(isnull(Sales),[Sales ($)], Sales) as Sales

Not applicable
Author


dragonauta wrote:if(isnull(Sales),[Sales ($)], Sales) as Sales


Hi Dragonauta,

When I use this I get an error that Sales ($) doesnt exist when it starts with the reports where its called Sales

David

GabrielAraya
Employee
Employee

Hi ... I think that the solution is very simple:

When you choose a XLS file to read with QVW in the script, you need to choose Labels: NONE in the "File Wizard", the name of your columns will be: @1, @2 .. etc and you need to drop the first rows that have the name of the columns, for example:



Directory;

LOAD

@1 as FieldName,

@2

,

@3

,

@4

,

@5

,

@6

,

@7

FROM [Llamadas_92380625 Ago.xls] ( biff, no labels, table is [Detalle de Llamadas$], filters (

Remove

(Row, Pos(Top, 1 )) )) ;

So, that way is not matter the name of the columns in your original XLS files ..

Gabriel





Not applicable
Author

Hi Gabriel,

Sometimes the simplest solution is the best, thanks for that

David