Expression for calculated Dimension?

[poluvidyasagar]

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

[sunny_talwar · Accepted solution]

Try this

=Aggr(If(Concat(DISTINCT Type, ',') = 'G,H', 'Shared', Type), Part)

[trdandamudi]

You don't have a field called 'Line'. I think it should be:

=if(Type='G' and Type='H', 'Shared', Type)

[poluvidyasagar]

Thanks Sunny.. It worked

[poluvidyasagar]

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

[sunny_talwar]

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;

[vishsaggi]

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;

[poluvidyasagar]

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