topic Re: How to use additional row using condition in existing pivot table? in QlikView

How to use additional row using condition in existing pivot table?

Wed, 22 Apr 2015 13:25:44 GMT

Hi,

I have tables as below in qvw.

Computer:
LOAD Region,
     Code,
     CustomerID,
     [Computer Stage],

Staff,
If(MixMatch([Computer Stage],'DELL'),Dual('Station 1',1),

If(MixMatch([Computer Stage],'HP',

'VAIO',

'INNOVA'),Dual('Former Station 1',2)

If(MixMatch([Computer Stage],'PC Application'),Dual('PC Ordered',3)

If(MixMatch([Computer Stage],

'Final DELL - Allocation',

'Final HP'),Dual('PC Delivered',4)

If(MixMatch([Computer Stage],'Dockure'),Dual('PC Price',5)

If(MixMatch([Computer Stage],

'TOSHIBA'),Dual('Confirmed',6)

If(MixMatch([Computer Stage],'DELLITE'),Dual('Monitored',7) [Computer Stage]))))))) as Flag,
Received
FROM
[Customer QVD\ComputerDet.qvd]
(qvd)

Location:
LOAD CustomerID,
     Region_From,
     Region_to,
     Transfer_Date,
     Transferby,
     Code
FROM
[Customer QVD\Location.qvd]
(qvd);

There is join between above two tables.

I have Pivot table built as below

Dimension= Flag

Dimension = Staff

Expression = Count(CustomerID)

Computer Stage	CEO	Manager	Team Leader
Station 1	34	89	89
Former Station 1	32	67	90
PC Ordered		5000	90
PC Delivered	3	45	900
PC Price	3456	43	45
Confirmed		34	89
Monitored	12	4568	908

my requirement is I need to add one more row into existing Pivot table

The condition used for the new row is

count(CustomerID) where Region_From ='US' to Region_to='UK'

as the two columns Region_From and Region_to are in separate table I am not sure how to use this condition. Please can anyone suggest me how to use this.

All I need the output as below

Computer Stage	CEO	Manager	Team Leader
Station 1	34	89	89
Former Station 1	32	67	90
PC Ordered		5000	90
PC Delivered	3	45	900
PC Price	3456	43	45
Confirmed		34	89
Monitored	12	4568	908
Moved from Location	45	89	90

Thanks.

Re: How to use additional row using condition in existing pivot table?

Wed, 22 Apr 2015 14:28:45 GMT

Can anyone suggest me please is it possible

Re: How to use additional row using condition in existing pivot table?

ramoncova06 — Wed, 22 Apr 2015 14:49:14 GMT

use set analysis

count({< Region_From = {'US'}, Region_to={'UK'}>}CustomerID)

Re: How to use additional row using condition in existing pivot table?

Wed, 22 Apr 2015 14:55:59 GMT

Thanks. It is not working. It is creating additional column not row. Any solution please

Re: How to use additional row using condition in existing pivot table?

Anonymous — Wed, 22 Apr 2015 15:00:50 GMT

Maybe by adding these records into your table in the script like below,

Map_Customer_USUK:

mapping

Load

CustomerID,

1 as flag

FROM

[Customer QVD\Location.qvd]

(qvd)

where Region_From='US' and Region_to='UK'

;

concatenate(Computer)

LOAD Region,

Code,

CustomerID,

'Moved from Location' as [Computer Stage],

Staff,

Dual('Moved from Location',8) as Flag

resident Computer

where ApplyMap('Map_Customer_USUK',CustomerID,0)=1

;

Re: How to use additional row using condition in existing pivot table?

buzzy996 — Wed, 22 Apr 2015 15:10:41 GMT

try to create expression and move that column as row in pivot table by simply dragging.

Re: How to use additional row using condition in existing pivot table?

ramoncova06 — Wed, 22 Apr 2015 15:16:00 GMT

sorry didn't read the whole requirement

by looking at it the best solution would be to modify you backend script to flag these

applying an if would exclude the users have another category

Re: How to use additional row using condition in existing pivot table?

Wed, 22 Apr 2015 15:35:23 GMT

Thanks. Tried this. Not working. do, I need to add anymore in script?

Re: How to use additional row using condition in existing pivot table?

Wed, 22 Apr 2015 15:36:18 GMT

Thanks. tried this. not working do , I need to add anymore in script?.

Re: How to use additional row using condition in existing pivot table?

Anonymous — Wed, 22 Apr 2015 15:43:20 GMT

would you be able to share some sample data?

Re: How to use additional row using condition in existing pivot table?

buzzy996 — Wed, 22 Apr 2015 15:54:33 GMT

qvw pls?