Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to load a file, process it and store it again in Excel to make it connectable with another program. To be able to load it into the other program, I need to add 9 rows and and add some text in A2-A4 and B2-B4. B2 is the only field which changes, as it has the date in it.
In Row 10 is the Headline of the processed data, 11-... contain the data.
Is there any way to do it?
Thanks for any ideas!
Michael
The simplest way is probably to load the excel twice. At first the few header rows and in the second real data-load you add the info from the first load, maybe with something like this:
temp: first 3 load A, B from xlsx (ooxml, no labels, table is XXX);
final: load *, fieldvalue('B', 1) as Date, fieldvalue('B', 2) as AccountingGroup, ...
from xlsx (ooxml, embedded lables, header 10 lines, , table is XXX);
If all your xlsx are so simple and have always the same structure you could try it in this way. Are they more dynamic you might need some more steps in between - like counting the records with/without content to define the first and header lines and/or looping through the temp-table and to read their content into n variables which are then used in the final load instead of the fieldvalue().
- Marcus
Hi,
if Excel doesn't work, then you all should drive me out of town with torches and stuff, because I am probably some kind of witch, because the program does exactly that.
Anyway:
Here's a sample of what I do in QV and how it should be exported to a spread sheet (csv or any other type of file doesn't matter to me). It's pretty basic because I just want to know how to get the file into the format of the Excel file which is also attached.
The yellow highlighted fields have to be added to the Header and the red fields shall be filled with Information to be able for the other program to read the data. The rest of the file should contain the data from the Qlikview script.
If you need more Information, just let me know.
Michael
Do you have an idea how I can do it in a simple manner? I have tried to do it for a couple of hours now, but I don't find a solution...
The simplest way is probably to load the excel twice. At first the few header rows and in the second real data-load you add the info from the first load, maybe with something like this:
temp: first 3 load A, B from xlsx (ooxml, no labels, table is XXX);
final: load *, fieldvalue('B', 1) as Date, fieldvalue('B', 2) as AccountingGroup, ...
from xlsx (ooxml, embedded lables, header 10 lines, , table is XXX);
If all your xlsx are so simple and have always the same structure you could try it in this way. Are they more dynamic you might need some more steps in between - like counting the records with/without content to define the first and header lines and/or looping through the temp-table and to read their content into n variables which are then used in the final load instead of the fieldvalue().
- Marcus