topic Re: Load Excel sheet up to a fixed value in QlikView

Load Excel sheet up to a fixed value

Wed, 11 Sep 2013 08:01:02 GMT

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.

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.

Re: Load Excel sheet up to a fixed value

Wed, 11 Sep 2013 08:47:34 GMT

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.

Re: Load Excel sheet up to a fixed value

Wed, 11 Sep 2013 09:26:19 GMT

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

Re: Load Excel sheet up to a fixed value

Wed, 11 Sep 2013 09:32:50 GMT

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

Peter_Cammaert — Wed, 11 Sep 2013 11:10:30 GMT

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

Re: Load Excel sheet up to a fixed value

Wed, 11 Sep 2013 12:15:53 GMT

Thank you all for your help,

i've done it like here

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

See last post from sampathpleasure1

My solution for this problem:

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;

@Erikvanhout

Have u an example for your way with the wizard? Probably i need some training with the wizard

Thanks & Best Regards,

Albert

Re: Load Excel sheet up to a fixed value

marcus_sommer — Wed, 11 Sep 2013 12:22:49 GMT

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);

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

Re: Load Excel sheet up to a fixed value

Wed, 11 Sep 2013 13:33:59 GMT

Hi Marcus Sommer,

works great. Thank you for your help.

But can't find it in the manual

Thanks & Best Regards,

Albert

Re: Load Excel sheet up to a fixed value

marcus_sommer — Wed, 11 Sep 2013 13:43:35 GMT

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

- Marcus