topic Re: Table Box Aggregation (Max) Single Line of Data in QlikView

Table Box Aggregation (Max) Single Line of Data

Wed, 11 Jun 2014 04:50:13 GMT

I have coded my LOAD script to pull a date when a task name is matched and I want to present each task date per project on a single row within a table (see below). I have done this in a report, outside of QlikView using the max aggregation, but I cannot get to this work in QV.

I would like the table to have one row for the project name and each date value listed in the same row.

Script:

Re: Table Box Aggregation (Max) Single Line of Data

jagan — Wed, 11 Jun 2014 05:16:19 GMT

Hi,

In Table box you cannot do the aggregation, instead you can use Straight table for this in Dimension tab specify the dimensions and in Expression tab give

=Max(FieldName)

Hope this helps you.

Regards,

Jagan.

Re: Table Box Aggregation (Max) Single Line of Data

tresB — Wed, 11 Jun 2014 05:19:27 GMT

To get the same here too you have to use an aggregation function, like:

Load

max(If(Match(....)) as [Engage Complete]

....

Resident <> Group By ....;

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 14:24:56 GMT

I tried using the straight table but it still staggers the date values...I want them to all be in the same row. Is this possible. gonna try a pivot table and manually tweak all the groupings.

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 14:37:10 GMT

I tried adding the MAX() but got an invalid expression error.

LOAD WorkspaceID,

[Milestone Name] as [Sacred Milestone],

[Task Planned Finish] as [Sacred Planned Finish],

max(if(MixMatch([Milestone Name], 'Engage Complete'), [Task Planned Finish])) as [Engage Complete],

max(if(MixMatch([Milestone Name], 'Requirements Complete'), [Task Planned Finish])) as [Requirements Complete],

if(MixMatch([Milestone Name], 'Design Complete'), [Task Planned Finish]) as [Design Complete],

if(MixMatch([Milestone Name], 'Build Phase Start'), [Task Planned Finish]) as [Build Phase Start],

if(MixMatch([Milestone Name], 'QA Entry'), [Task Planned Finish]) as [QA Entry],

if(MixMatch([Milestone Name], 'QA Pass'), [Task Planned Finish]) as [QA Pass],

if(MixMatch([Milestone Name], 'Final Configurations delivered to Ops'), [Task Planned Finish]) as [Final Configurations delivered to Ops],

if(MixMatch([Milestone Name], 'Final DBINT (webstore) delivery to Ops'), [Task Planned Finish]) as [Final DBINT (webstore) delivery to Ops],

if(MixMatch([Milestone Name], 'UAT Start'), [Task Planned Finish]) as [UAT Start],

if(MixMatch([Milestone Name], 'UAT Pass'), [Task Planned Finish]) as [UAT Pass],

if(MixMatch([Milestone Name], 'Soft Launch'), [Task Planned Finish]) as [Soft Launch],

if(MixMatch([Milestone Name], 'Hard Launch'), [Task Planned Finish]) as [Hard Launch] Resident Milestones group by workspaceID;

Re: Table Box Aggregation (Max) Single Line of Data

PrashantSangle — Thu, 12 Jun 2014 14:41:29 GMT

Hi,

Can You share sample app.

Regards,

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 14:45:01 GMT

Hi,

Are you sure that you define this dimension as Expression Total in "Total Mode"?:

Regards

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 14:52:27 GMT

yeah, but then my [Project Name] does not appear on the same row as the dates. I tried creating another expression max([Project Name]) but it was blank.

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 14:57:18 GMT

Maybe you know that, but the first line in your table is the "Grand Total" line.

Because of that, it is appearing both dates in one line and does not appear the Project Name.

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 15:07:27 GMT

I was hoping I could get the project name in the grand total line too, but no luck.

Is there no way to get the dates to line up in the same row if I were to take away the grand totals?

Re: Table Box Aggregation (Max) Single Line of Data

PrashantSangle — Thu, 12 Jun 2014 15:20:19 GMT

Hi,

Can You tell us,

What is your dimension?

and

What is your expressions?

Regards,

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 15:25:32 GMT

I was able to get it to work, can i remove the grand totals row now?

Re: Table Box Aggregation (Max) Single Line of Data

PrashantSangle — Thu, 12 Jun 2014 15:26:56 GMT

Hi,

Yes,

Just Select No Total Instead of Expression Totals

In Expression Tab

Regards,

Re: Table Box Aggregation (Max) Single Line of Data

Thu, 12 Jun 2014 15:41:34 GMT

Dear,

I make an example and it work fine!

I send you my files and printscreen. I hope this helps you

Regards