Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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.

alwinsch
Contributor

Create total column dynamically depending on existing of files

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Create total column dynamically depending on existing of files

Try using as expression for your total:

=rangesum(January,February,March,April)

6 Replies
MVP
MVP

Create total column dynamically depending on existing of files

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
Contributor

Create total column dynamically depending on existing of files

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

MVP
MVP

Re: Create total column dynamically depending on existing of files

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
Contributor

Re: Create total column dynamically depending on existing of files

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

MVP
MVP

Re: Create total column dynamically depending on existing of files

Try using as expression for your total:

=rangesum(January,February,March,April)

alwinsch
Contributor

Re: Create total column dynamically depending on existing of files

Thank you it worked perfect!