Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Variations in names on a batch load

how about something like

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

4 Replies
Not applicable

Variations in names on a batch load

how about something like

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

Not applicable

Variations in names on a batch load


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

Highlighted
Employee
Employee

Variations in names on a batch load

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

Variations in names on a batch load

Hi Gabriel,

Sometimes the simplest solution is the best, thanks for that

David