topic Re: How to create summary from attached data module in QlikView

How to create summary from attached data module

vikasmahajan — Fri, 24 Jun 2016 04:49:49 GMT

Dear All,

I have following data model , Which is linked with cost centers using qualify we have attached more than 20 opportunity like this now here my requirement is create a summary like this

Opportunity Charges

------------------- ----------------

Opportunity1 545.1

Opportunity2 538.

-------------------------------------

Total 1083.1

Re: How to create summary from attached data module

varshavig12 — Fri, 24 Jun 2016 05:03:09 GMT

Create a flag

Re: How to create summary from attached data module

sunny_talwar — Fri, 24 Jun 2016 05:04:21 GMT

You want it in a straight table?

Re: How to create summary from attached data module

varshavig12 — Fri, 24 Jun 2016 05:04:47 GMT

Try this.

Re: How to create summary from attached data module

sunny_talwar — Fri, 24 Jun 2016 05:06:41 GMT

Like this?

Re: How to create summary from attached data module

varshavig12 — Fri, 24 Jun 2016 05:08:46 GMT

You want the same data model ?

Re: How to create summary from attached data module

vikasmahajan — Fri, 24 Jun 2016 05:30:47 GMT

yes want in straight table

Re: How to create summary from attached data module

vikasmahajan — Fri, 24 Jun 2016 06:40:17 GMT

Thanks Sunny , Actually in dimension i want opportunity and in expression sum(Charges) this is only one filed in demo data but i have to define 3 more with expression .

Vikas

Re: How to create summary from attached data module

sunny_talwar — Fri, 24 Jun 2016 06:42:03 GMT

Then look at what Varsha has provided below

Re: How to create summary from attached data module

vikasmahajan — Fri, 24 Jun 2016 06:42:27 GMT

Thanks Varsha like sunny given i want but in dimension i want opportunity i.e opportunity1 , opportunity2

and in expression sum( ) of each opportunity.

Vikas

Re: How to create summary from attached data module

varshavig12 — Fri, 24 Jun 2016 06:47:56 GMT

This is what you want,

Right ?

Re: How to create summary from attached data module

vikasmahajan — Fri, 24 Jun 2016 06:52:51 GMT

Right ? But you removed table names in script without removing tables names will it possible ?

Re: How to create summary from attached data module

varshavig12 — Fri, 24 Jun 2016 07:10:46 GMT

Do you mean that you don't want to join the tables ? Then i guess we need to use Qualify

And do you have cost center in your master table.

Coz it is not there in the excel, but do exist in your data model.

Re: How to create summary from attached data module

vikasmahajan — Fri, 24 Jun 2016 07:34:53 GMT

yes it is linked into original data model.

Vikas

Re: How to create summary from attached data module

varshavig12 — Fri, 24 Jun 2016 08:34:11 GMT

Something like this:

But this becomes too complicated,

Don't you think so ?

Re: How to create summary from attached data module

varshavig12 — Fri, 24 Jun 2016 08:37:35 GMT

Oh then i guess, Sunny can help you in better way.

Re: How to create summary from attached data module

qliksus — Mon, 27 Jun 2016 08:43:45 GMT

May be something like this

Script :

QUALIFY * ;
UNQUALIFY Key,Charge;

Opportunity1:
LOAD
     [Cost Center] as Key,
     [Cost Center],
     Hostname,
     UpdateStatus,
     Manager,
     Application,
     Env ,
     Charge


FROM
[OpportunityData.xlsx]
(ooxml, embedded labels, table is Opportunity1);

Opportunity2:

LOAD
     [Cost Center] as Key,
     [Cost Center],
     Hostname,
     UpdateStatus,
     Manager,
     Application,
     Env ,
     Charge

FROM
[OpportunityData.xlsx]
(ooxml, embedded labels, table is Opportunity2);

QUALIFY * ;
UNQUALIFY Key,Charges,Opportunity1;

load
      'Opportunity1' as Opportunity1,
      Charge as Charges,
       Key
Resident Opportunity1 ;

load
     'Opportunity2' as Opportunity1,
      Charge as Charges,
     Key
Resident Opportunity2 ;

DROP Field Charge ;