Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a question:
In this sample script:
DATA:
LOAD
*
Inline [
Campo1, Valor1
AAA, 111
BBB,222
]
;
DATA2:
LOAD
Campo1,
Valor1
Resident DATA
Where Campo2 = 'AAA'
;
DROP Table DATA;
Can I put a IF condition in WHERE to let the two names (Campo1 and Campo2)?
The problem is when loading some excel files in a loop, the field name is changing.
Thank you.
If you're sure that it's always the first column that you'll use in a WHERE clause, then that will work too. The only thing is that performance will not be ideal, but that isn't much of a problem if your Excel files aren't too big.
BTW In the FOR loop, add a DROP Table DATA; statement after the LET vFile... line or you may be joining all columns from all Excel files into a single DATA table. I'm assuming that the autoconcatenate that will happen on your DATA2 table is intended?
Peter
Hi,
can you explain better what do you mean "some excel files in a loop, the field name is changing".
Or attach sample data?
Where did Campo2 came from? I do not see that in your original DATA table?? You cannot refer a field in your resident load if they are not present in your original field.
Best,
Sunny
Hi,
Please provide some sample data for better explanation,
Thanks
Hi,
It was a "sample".
I do loop and I load some excel like:
For Each vFile in filelist *
DATA:
LOAD
Max(Floor([Creada])) as "Max_Fecha"
FROM [$(vFile)]
Where NOT [Campo1] like 'Generado*';
Next vFile;
The problem is when in excel Campo1 is renamed as Campo2.
Have I do a preload of all excel renaming the field name to Campo1?
Sorry for my poor explanation.
Instead of referencing the field using embedded field names, you can use the table columns, like A, B ,C or @1, @2 ... field references (but depending on the Excel file version also).
You need to skip the row with the embedded lables then. I would suggest to use the table wizard to create a propert LOAD statement.
This will only work if your columns in the Excel may have a changing header, but same semantics (i.e. first column is always Date field etc.).
Hi,
In this case it should be a solution, but if you have to load several fields the field references will be not clear.
I have tried with ALIAS, previous LOAD, but not working because renames after load and I need a previous rename field.
Thank you.
If you can detect the correct name of the column-to-be-used in the WHERE clause from the actual filename in $(vFile), you could set a second variable e.g. vColumn to the name of the correct column to use. For example:
For Each vFile in filelist *
IF check-if-vFile-meets-certain-conditions THEN
SET vColumn = Campo1;
ELSE
SET vColumn = Campo2;
END IF;
DATA:
LOAD $(vColumn) AS Campo1,
Max(Floor([Creada])) as "Max_Fecha"
FROM [$(vFile)]
Where NOT [$(vColumn)] like 'Generado*';
Next vFile;
And how about putting all Excel source files back in-line by renaming their columns once and for all? This is a one-time only effort, but your load scirpt will become much simpler.
Peter
This is what I´m doing right now.
Just thinking in other solution.