Create total column dynamically depending on existing of files

[alwinsch]

Hi all,

I need some help with tables:
In my script I refer to a folder on a network with XLSX files
always of the same type: 79706 01.xlsx or 78354 01.xlsx or 79706 02.xlsx

Where 79706 and 78354 is the unique officeID
and 01, 02 stands for the month.
At the end of beginning of April we create a new file “79706 03.xlsx”

Now in my table I want a column for each month

ID	January	February	March	Total
79706	12	8	0	20
78354	5	13	0	18

At this time my total = January + February
next month it should be total = January + February + march
If I say at this time total = January + February + march à you get stupid values, because the file does not exist.
I want to create total once and it should update dynamically.

Please I need some help with this?

regards,

Alwin

[swuehl]

Maybe you can load your excel files with a wildcard, reading in all excel files in that directory, and then parsing the filename for ID an Month name, something along this lines:

LOAD

Value,

subfield(FileName(),' ',1) as ID,

Text(date(addmonths(0,mid(subfield(FileName(),' ',2),1,2)),'MMMM')) as Month

FROM

[*.xlsx]

(ooxml, embedded labels, table is Tabelle1);

Then create a pivot table with dimensions ID an Month and do a partial sum on dimension Month in presentation tab.

Hope this helps,

Stefan

[alwinsch]

Hi,

I already have a load statement which loads all Excel files from a specific folder

LOAD

mid(FilebaseName(),3,5) as "PortimaID",right(FileBaseName(),2) as "maand",

   FROM

(ooxml, embedded labels, table is [Selectie en Rapport]);

But the problem is... that the file for next month does not exist for the moment.

And thereby my total column shows NULL values.

because total = file 01 + file 02 + file03

but file 03 exists only within 14 days...

So for the moment i have to say:

total = file01+file02

and i have to change it next month.

That is what i don't want. I want to create the qvw file now and never change it again.

so where total = file01 + file02+ file03 + file04+...

regards,

Alwin

[swuehl]

So you are hardcoding your total as expression? And also the Months are separate expressions?

Try using a pivot with two dimensions, ID and Month (resp. PortimaID and maand) and one expression, I used the sum(Value).

Then use a partial sum (Total) in presentation tab for Month dimension. This should automatically reflect only the exisiting Months.

Or maybe I have misunderstood your problem?

[alwinsch]

I did a makeover of your files

add my table and syntax how  i have it at the moment.

when you see at the expression total growth. there is my problem.

thanks for your help.

Alwin

[swuehl · Accepted solution]

Try using as expression for your total:

=rangesum(January,February,March,April)

[alwinsch]

Thank you it worked perfect!