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: 
michael_wernsing
Contributor II
Contributor II

QVW excel storage with added headers and fields

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

 

 

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

5 Replies
bramkn
Partner - Specialist
Partner - Specialist

Hi,

It is not possible to store into Excel files from within the QV scripting.
You can store into CSV and QVD.
The modification of the data is of course possible within the script. If you need any help with this please provide some sample data and your wishes.
michael_wernsing
Contributor II
Contributor II
Author

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

 

 

bramkn
Partner - Specialist
Partner - Specialist

You can read the xlsx data no worries. But storing into xlsx isn't supported.
Exporting to Excel is possible from the dashboard but not the script.

To add this kind of header data you can for example create an inline table or use autogenerate to create the rows you want. After you created these rows you can concatenate your data to it and store it into CSV.
michael_wernsing
Contributor II
Contributor II
Author

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...

marcus_sommer

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