topic Re: Aggregations and group by in QlikView

Aggregations and group by

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

Hi I am attaching the excel file where I needed some help in qlik coding.

In the attached excel file in Data tab ->
1. I need to derive the calculations from column T based on S column till X column in qlik. I have done using aggregations in front end and it’s very slow.

2. In pivot tab , I need to calculate average of action duration for three pivot tables for three stages based on the filters mentioned in pivot sheet.

3. Only for cycle time we need to take maximum of closed time stamp for particlular invoice which was terminated at all stages i.e approval or assign approver.

Please help me in solving this it will be very helpful for me.

Thanks a lot.

Re: Aggregations and group by

andrea0901 — Wed, 06 Feb 2019 14:17:13 GMT

Hi All,
Can anyone help me on this issue. Its little urgent.

Re: Aggregations and group by

sunny_talwar — Thu, 07 Feb 2019 13:47:49 GMT

Try this

Table:
LOAD IN#, 
     Workflow.companyCode, 
     Workflow.fiDocumentNumber, 
     Workflow.mmDocumentNumber, 
     Workflow.invoiceLineItemNumber, 
     Workflow.Instance_Type, 
     Workflow.blockType, 
     Workflow.Status, 
     Recon.INVOICE_LINEITEM_AMOUNT_EUR, 
     Recon.INVOICE_LINEITEM_AMOUNT, 
     Recon.CURRENCY, 
     Events.ACTION, 
     Events.ASSIGNED_TO_ROLE, 
     Events.ASSIGNED_TO_USER, 
     Events.ASSIGNED_TIMESTAMP, 
     Events.CLOSED_BY_USER, 
     Events.CLOSED_TIMESTAMP,
     Action_v2,
     Workflow.mmDocumentNumber & '_' & Workflow.invoiceLineItemNumber & '_' & Action_v2 as Workflow_Action_v2,
     Workflow.mmDocumentNumber & '_' & Workflow.invoiceLineItemNumber as [Workflow+item],
     Workflow.mmDocumentNumber & '_' & Workflow.invoiceLineItemNumber& '_' & Workflow.blockType as [Workflow item with workflow block],
     Events.CLOSED_TIMESTAMP - Events.ASSIGNED_TIMESTAMP as Duration,
     Country,
     Market,
     Region,
     Kernel,
     [Infosys Center],
     [Workflow ended]
FROM
[..\..\Downloads\Testing.xlsx]
(ooxml, embedded labels, table is Data);

Left Join (Table)
LOAD Workflow_Action_v2,
	 Sum(Duration) as [Action duration]
Resident Table
Group By Workflow_Action_v2;

Left Join (Table)
LOAD [Workflow+item],
	 Sum(Duration) as [Workflow duration]
Resident Table
Group By [Workflow+item];

You are doing vlookup for some of the field such as Action_v2. I didn't have the file from where you are doing this vlookup, so I picked it up directly from the Excel provided.

Re: Aggregations and group by

andrea0901 — Fri, 08 Feb 2019 02:46:34 GMT

Hi , thank you I will do this.

Re: Aggregations and group by

andrea0901 — Fri, 08 Feb 2019 07:44:17 GMT

Hi sunny,

Thanks for ur help.

I am finding a new challenge here.

In my excel attached previuosly, there is a tab , PIVOT.

i am creating three scenarios.

1st scenario:

For each scenario i need to harcode Workflow.Status as terminated and ACTION_V2 = Approver.

i wrote in straight table as

=Avg({<Workflow.Status = {'Terminated'}, Action_v2 = {'Approver'}>}[Action duration])

but i am not getting any values if i write like above.

similarly for second scenrio as well.

Please help me out.

Re: Aggregations and group by

sunny_talwar — Fri, 08 Feb 2019 12:13:36 GMT

I am not sure I follow, would you be able to show an image of what you get and point out what exactly isn't right?

Re: Aggregations and group by

andrea0901 — Mon, 11 Feb 2019 02:49:59 GMT

Hi Sunny,

It is working now. Thanks for your help.