topic Totalling various columns from within concatenated link tables in QlikView

Totalling various columns from within concatenated link tables

Thu, 17 Mar 2011 12:45:15 GMT

Hi all,

I hope someone can make sense of this.

I have expression 1 which is totalling various numbers from our ODBC database:

SUM(IF(Week_Class ='Current Week',(basichours+nextdayhours+othours+nextdayot+salaryhours)))

This works fine . I want to be able to add some columns to this grand total from table files. I have manually entered these table files and concatenated into a link table.

These are also working fine on their own:

expression 2

SUM(IF(Week_Class ='Current Week',(MitchPerm+MitchBank+MitchAgy+GarPerm+GarBank+GarAgy+HomeCarePerm+HomeCareBank+HomeCareAgy)))

But if I try to add the above two sets of columns together in 1 sum if expression, I get 0.

The columns in expression 2 do not have corresponding values in the columns in expression 1 so I think this is where its falling down.

Is there something I can state in the expression such a distinct command which will sum the applicable columns together.

Unfortunately, I cannot post sample data due to the vastness of this database. There are a lot of linked tables but I 'think' this is a syntax issue rather than relationship (?) as the 2 expressions are working seperately.

Any help would be gratefully appreciated.

Matt

Totalling various columns from within concatenated link tables

Miguel_Angel_Baeyens — Thu, 17 Mar 2011 12:59:30 GMT

Hello Matt,

I'd translate your expression 1 into set analysis to get something like

SUM({< Week_Class = {'Current Week'} >} basichours + nextdayhours + othours + nextdayot + salaryhours)

And likewise with expression 2:

SUM({< Week_Class = {'Current Week'} >} MitchPerm + MitchBank + MitchAgy + GarPerm + GarBank + GarAgy + HomeCarePerm + HomeCareBank + HomeCareAgy)

If these are the only columns in your chart, you can use a new expression to get the sum of both

ColumnNo(1) + ColumnNo(2)

Hope this helps.

Totalling various columns from within concatenated link tables

Thu, 17 Mar 2011 13:49:23 GMT

Thanks for the reply Miguel.

Your suggestions do work seperately as stand alone expressions but I need to combine the 2 sets of totals to display in 1 LED chart box so I dont think it will work in this case.

Totalling various columns from within concatenated link tables

Miguel_Angel_Baeyens — Thu, 17 Mar 2011 14:06:15 GMT

Hello Matt,

Ok, so both in one look like

SUM({< Week_Class = {'Current Week'} >} basichours + nextdayhours + othours + nextdayot + salaryhours + MitchPerm + MitchBank + MitchAgy + GarPerm + GarBank + GarAgy + HomeCarePerm + HomeCareBank + HomeCareAgy)

Hope that helps.

Totalling various columns from within concatenated link tables

Thu, 17 Mar 2011 14:31:25 GMT

Thanks Miguel again but I wish it was that easy .

When I combine the two sets of columns in 1 Sum expression, the total is zero. I have tried to attach a sample from a table exported to Excel:

the first column is where the Week Class is current or week-1 etc,

the second column is a sample column MitchPerm (picked from expression 2 as example)

the third column is sample from expression 1

the fourth column is sum of Mitch Perm + expression 1 sample but this is where it shows zero!

I'm close to giving this job up and becoming a hermit lumberjack or something

Totalling various columns from within concatenated link tables

Miguel_Angel_Baeyens — Thu, 17 Mar 2011 15:07:11 GMT

Hi Matt,

If possible, some sample data would be better so we can check how the document is associating fields and why aren't you getting the values you want. My guess is that the table that stores Mitch Perm is not related to the one that stores basic hours and so.


cayfmatt wrote:I'm close to giving this job up and becoming a hermit lumberjack or something

You're not the first I've heard of this before. Anyway don't give up!

Totalling various columns from within concatenated link tables

Thu, 17 Mar 2011 16:37:56 GMT

Thanks Miguel. Unfortunately I am accessing the very large Qlikview file through a Terminal Server and am unable to post any sample data.