topic Re: Creating a summary table from a straight table in QlikView

Creating a summary table from a straight table

sayadutt — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a straight table and looking for a summary table as shown below:

Now I am looking for a summary table which will group the projects and show the all the issues comma separated.

Also, status column should be based on :

Complete - once ALL the individual status for that project move to "complete".

In progress - when ANY of the individual status for that project is still under to "in progress".

Not Started - when All status are Not started

Re: Creating a summary table from a straight table

Vegar — Tue, 30 Jul 2019 18:45:40 GMT

Try to create a straight table with the field Project as a dimension and the following two expressions.

Status:

If (IsNull(Only(Status)), 'In progress', only(Status))

Issues:

Concat(Distinct Issues, ', ')

Re: Creating a summary table from a straight table

sayadutt — Tue, 30 Jul 2019 18:56:22 GMT

Hi Vegar,

Thanks for your reply.

For:

Issues:

Concat(Distinct Issues, ', ')

only commas comes when there are blank values under Status. Can you please guide how I can hide the commas when Status is blank.

Re: Creating a summary table from a straight table

sunny_talwar — Tue, 30 Jul 2019 19:00:46 GMT

May be this

Re: Creating a summary table from a straight table

sunny_talwar — Tue, 30 Jul 2019 19:02:57 GMT

You can try this

Concat(DISTINCT {<Issues = {"=Len(Trim(Issues)) > 0"}>} Issues, ', ')

Re: Creating a summary table from a straight table

Vegar — Tue, 30 Jul 2019 19:11:03 GMT

I believe that @sunny_talwar suggested adjustment should work.

Re: Creating a summary table from a straight table

sayadutt — Tue, 30 Jul 2019 19:29:01 GMT

Thanks it worked.

Can you help me with another column.

In same table I have another column as :

Available

yes

I want to put the count of yes only in the summary table like

Count of Available

have tried below as expression: no results were returned:

if(Available='Yes', count(project))

Re: Creating a summary table from a straight table

sayadutt — Tue, 30 Jul 2019 19:32:57 GMT

Thanks it worked.

Can you help me with another column.

In same table I have another column as :

Available

yes

I want to put the count of yes only in the summary table like

Count of Available

have tried below as expression: no results were returned:

if(Available='Yes', count(project))

Re: Creating a summary table from a straight table

sunny_talwar — Tue, 30 Jul 2019 19:35:38 GMT

May be this

Count({<Available = {'Yes'}>} project)

Re: Creating a summary table from a straight table

sayadutt — Tue, 30 Jul 2019 19:46:32 GMT

Wonderful Sunny.

last question: similar column I need to add on a date field.

Need is to display the largest date from the table.

7/30/19

7/31/19

7/29/19

In this case, only 7/31 should be displayed as thats the greatest from the list.

Re: Creating a summary table from a straight table

sunny_talwar — Wed, 31 Jul 2019 11:26:46 GMT

May be just Max(Date)