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

[#1]

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.

[#2]

Can anyone suggest me please is it possible

[ramoncova06]

use set analysis

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

[#4]

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

[Anonymous]

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

;

[buzzy996]

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

[ramoncova06]

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

[#8]

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

[#9]

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

[Anonymous]

would you be able to share some sample data?