Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to achieve the following:
My Data:
PartMasterTable:
Part | Category | Day1 | Day2 | Day3 | Day4 | Use Location |
40179255 | A | 20 | 15 | 15 | 15 | G50021 |
40179255 | A | 20 | 15 | 15 | 15 | G53123 |
40179255 | A | 20 | 15 | 15 | 15 | H13212 |
61697461 | B | 10 | 10 | 20 | 15 | G53123 |
61697461 | B | 10 | 10 | 20 | 15 | G53423 |
61697461 | B | 10 | 10 | 20 | 15 | G53124 |
Data:
Load
Part,
Use Location,
Left(Use Location) as Type
from [PartMasterTable];
I am trying to create Straight Table Chart:
Using Dimensions:
1. Part
2. Use Location
3. =if(Type='G' and Type ='H', 'Shared', Type) - This one does not work..
I am trying to display like below:
Part | Use Location | Type |
40179255 | G50021 | Shared |
40179255 | G53123 | Shared |
40179255 | H13212 | Shared |
61697461 | G53123 | G |
61697461 | G53423 | G |
61697461 | G53124 | G |
How can I achieve this?
In addition, can i achieve this during the data load it self?
Thanks,
Vidya
Message was edited by: Vidya Sagar Polu
Try this
=Aggr(If(Concat(DISTINCT Type, ',') = 'G,H', 'Shared', Type), Part)
Try this
=Aggr(If(Concat(DISTINCT Type, ',') = 'G,H', 'Shared', Type), Part)
You don't have a field called 'Line'. I think it should be:
=if(Type='G' and Type='H', 'Shared', Type)
Thanks Sunny.. It worked
Hi Sunny,
I have similar problem with respect to the post.
How can i use the same logic in during script load?
Here is my code:
LineMap:
Mapping
Load * Inline [
Left, Line
1, G
2, H
5, G
B, G
D, G
];
MPOT:
LOAD
[Part number*] as PartNumber,
[Location *] as [Location],
ApplyMap('LineMap',left([Location *],1),'other') as LineType
FROM MPOT;
MPOT1:
Load
PartNumber,
if(concat(Distinct LineType,',')='G,H','Shared',LineType) as LineItem
/* it gives me error on this line. It says invalid expression */
Resident MPOT
Group By PartNumber;
Drop Table MPOT;
MPOT2:
Mapping Load
PartNumber,
LineItem
Resident MPOT1;
DROP Table MPOT1;
It gives me error if i use this script during load.
Can you please tell what is wrong here;
Thanks,
Vidya
Try like this
Table:
LOAD *,
Left([Use Location], 1) as LineType;
LOAD * INLINE [
PartNumber, Category, Day1, Day2, Day3, Day4, Use Location
40179255, A, 20, 15, 15, 15, G50021
40179255, A, 20, 15, 15, 15, G53123
40179255, A, 20, 15, 15, 15, H13212
61697461, B, 10, 10, 20, 15, G53123
61697461, B, 10, 10, 20, 15, G53423
61697461, B, 10, 10, 20, 15, G53124
];
Left Join (Table)
LOAD PartNumber,
If(Concat(DISTINCT LineType, ',') = 'G,H', 'Shared', Only(LineType)) as LineItem
Resident Table
Group By PartNumber;
May be try this?
MPOT:
LOAD
[Part number*] as PartNumber,
[Location *] as [Location],
ApplyMap('LineMap',left([Location *],1),'other') as LineType
FROM MPOT;
SET vConcat = "=Concat(DISTINCT LineType, ',')";
MPOT1:
LOAD PartNumber,
if($(vConcat) ='G,H','Shared',LineType) as LineItem
Resident MPOT;
Drop Table MPOT;
This worked as well!
Thanks Vishwa! However, i have extended my logic and it gives me error. Can you tell what is wrong on the highlighted expression?
LineMap:
Mapping
Load * Inline [
Left, Line
1, G
2, H
5, G
B, G
D, G
6, WP
4, WP
];
MPOT:
/*Mapping */ LOAD
[Part number*] as PartNumber,
[Location *] as [Location],
ApplyMap('LineMap',left([Location *],1),'other') as LineType,
Right([Location *],2) as WPCheck
FROM
//[..\8.Import\Manage Part Order Type.xlsx]
[$(vL.ImportFolder)/Manage Part Order Type.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Manage Part Order Type]) ;
SET vConcat = "Concat(Distinct LineType, ',')";
MPOT1:
Load
PartNumber,
if($(vConcat)='G,H,WP' or $(vConcat)='G,H' or $(vConcat)='G,WP' or $(vConcat)='H,WP' ,'Shared',
if(only(LineType)='G' and WPCheck='WP','WP', only(LineType))) as LineItem /*It gives me error on this expression*/
Resident MPOT
Group By PartNumber;
//Drop Table MPOT;
Thanks,
Vidya