Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Load Excel sheet up to a fixed value

Hi everyone,

i'm new in QlikView and i've a problem with loading an Excel-Sheet(s):

My datasource is an Excel file which contains 12 sheets (Januar - Dezemeber). In every sheet is a start value ("Datum") and an end value ("Durchschnitt"). How can I load all data in this sheets between this 2 keywords?

My plan was to set 'header is 6 lines' and then to load until the value 'Durchschnitt' in the column 'F1 as Datum' appears.

DO

Argus_Propan:

LOAD F1 as Datum,

     Low as CIF_ARA_LARGE_Low,

     High as CIF_ARA_LARGE_High,

     Mittelw. as CIF_ARA_LARGE_Avg,

     [Mittelw. Kum.] as CIF_ARA_LARGE_Avg_Cum,

     Low1 as FCA_ARA_RAIL_Low,

     High1 as FCA_ARA_RAIL_High,

     Mittelw.1 as FCA_ARA_RAIL_Avg,

     [Mittelw. Kum.1] as FCA_ARA_RAIL_Avg_Cum,

     Low2 as FOB_ARA_BARGE_Low,

     High2 as FOB_ARA_BARGE_High,

     Mittelw.2 as FOB_ARA_BARGE_Avg,

     [Mittelw. Kum.2] as FOB_ARA_BARGE_Avg_Cum

FROM

(biff, embedded labels, header is 6 lines, table is September$);

LOOP while (F1 = 'Durchschnitt');

STORE Argus_Propan INTO $(vQVDPath)\Argus_Propan.qvd;

DROP TABLE Argus_Propan;

Your help is much appreciated!

Albert E.

Tags (4)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Load Excel sheet up to a fixed value

I suggest you could it better load with an approach like this:

for each vTabSheet in 'Januar', 'Februar', ... and so on

     Argus_Propan:

     first 31 // number of max. rows

     Load ...

     From  

     (biff, embedded labels, header is 8 lines, table is $(vTabSheet))

     Where isnum(F1);

next


store ...

drop ...


Your table will be a crosstable - perhaps it is a better way this table to convert in a normal table. You could here use the crosstable-wizard. All parts from this suggestion you could find in the manual.

- Marcus

8 Replies
Not applicable

Re: Load Excel sheet up to a fixed value

Hi Albert,

When you're in the script editor and you've selected the Argus Propan 2013.xls file (using the Table Files... button in the script editor) you can enter the File Wizard (by selecting the Next> button).

With a conditional delete you can leave out the row containing Durchschnitt.

Hope this helps.

Not applicable

Re: Load Excel sheet up to a fixed value

Hi Erik,

thanks for your fast reply. I tried the Wizard already - thanks for this tip.

But I forgot to mention that it should be kept variable and all the sheets are updated every day for the curent month yearly.

If I use the wizard, i would have to generate the load every year new (Argus Propan 2014.xls) -> the number of data varies from month to month and from year to year - but the two conditions are always the same.
My idea was it to go thorugh a sheet and check the start condition ('Datum') and the end condition ('Durchschnitt') for each month and load the range between this two conditions.


Thanks & Best Regards,

Albert

Not applicable

Re: Load Excel sheet up to a fixed value

Hi Albert,

With the conditional delete - the wizard will always look for the row containing the end condition Durchschnitt.

You can also use a conditional to delete the header rows (or you must make sure always the same amount of header rows are used).

All other rows will be added to your table.

You can use a variable to store the filename.

That way you only have to change the variable once a year.

You can even store this vaiable in a settings-file.

Re: Load Excel sheet up to a fixed value

How about starting to read at row 8 and continue reading while the first column contains a value that can be interpreted as a numerical value using IsNum()? Internally Excel represents dates as the integer part of a floating point value. 

Luck,

Peter

Not applicable

Re: Load Excel sheet up to a fixed value

Thank you all for your help,

i've done it like here

http://community.qlik.com/message/327956#327956

See last post from

Argus_Data:

LOAD F1 as Datum,

     Low as CIF_ARA_LARGE_Low,

     High as CIF_ARA_LARGE_High,

     Mittelw. as CIF_ARA_LARGE_Avg,

     [Mittelw. Kum.] as CIF_ARA_LARGE_Avg_Cum,

     Low1 as FCA_ARA_RAIL_Low,

     High1 as FCA_ARA_RAIL_High,

     Mittelw.1 as FCA_ARA_RAIL_Avg,

     [Mittelw. Kum.1] as FCA_ARA_RAIL_Avg_Cum,

     Low2 as FOB_ARA_BARGE_Low,

     High2 as FOB_ARA_BARGE_High,

     Mittelw.2 as FOB_ARA_BARGE_Avg,

     [Mittelw. Kum.2] as FOB_ARA_BARGE_Avg_Cum,

     RecNo() as Range

FROM

(biff, embedded labels, header is 5 lines, table is September$);

max:

load max(Range) as ad Resident Argus_Data;

let b=Peek('ad',0);

drop Table max;

let c = Lookup('Range','Datum','Durchschnitt','Argus_Data');

Argus_Propan:

NoConcatenate

LOAD * Resident Argus_Data Where Range < $(c); //and no< $(d);

STORE Argus_Propan INTO $(vQVDPath)\Argus_Propan.qvd;

DROP TABLE Argus_Data, Argus_Propan;

Thanks & Best Regards,

Albert

MVP & Luminary
MVP & Luminary

Re: Load Excel sheet up to a fixed value

I suggest you could it better load with an approach like this:

for each vTabSheet in 'Januar', 'Februar', ... and so on

     Argus_Propan:

     first 31 // number of max. rows

     Load ...

     From  

     (biff, embedded labels, header is 8 lines, table is $(vTabSheet))

     Where isnum(F1);

next


store ...

drop ...


Your table will be a crosstable - perhaps it is a better way this table to convert in a normal table. You could here use the crosstable-wizard. All parts from this suggestion you could find in the manual.

- Marcus

Not applicable

Re: Load Excel sheet up to a fixed value

Hi Marcus Sommer,

works great. Thank you for your help.

But can't find it in the manual

Thanks & Best Regards,

Albert

MVP & Luminary
MVP & Luminary

Re: Load Excel sheet up to a fixed value

I meant if you search in manual or F1 after "for each" / "first" / "crosstable" you will find some explanations and examples.

- Marcus