Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can anyone suggest me please is it possible
use set analysis
count({< Region_From = {'US'}, Region_to={'UK'}>}CustomerID)
Thanks. It is not working. It is creating additional column not row. Any solution please
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
;
try to create expression and move that column as row in pivot table by simply dragging.
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
Thanks. Tried this. Not working. do, I need to add anymore in script?
Thanks. tried this. not working do , I need to add anymore in script?.
would you be able to share some sample data?