topic Re: Expression for calculated Dimension? in QlikView

Expression for calculated Dimension?

poluvidyasagar — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression for calculated Dimension?

sunny_talwar — Tue, 26 Jun 2018 17:57:22 GMT

Try this

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

Re: Expression for calculated Dimension?

trdandamudi — Tue, 26 Jun 2018 17:57:30 GMT

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

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

Re: Expression for calculated Dimension?

poluvidyasagar — Tue, 26 Jun 2018 18:19:47 GMT

Thanks Sunny.. It worked

Re: Expression for calculated Dimension?

poluvidyasagar — Wed, 29 Aug 2018 19:33:45 GMT

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

Re: Expression for calculated Dimension?

sunny_talwar — Wed, 29 Aug 2018 19:44:07 GMT

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;

Re: Expression for calculated Dimension?

vishsaggi — Wed, 29 Aug 2018 19:54:16 GMT

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;

Re: Expression for calculated Dimension?

poluvidyasagar — Wed, 12 Sep 2018 18:27:12 GMT

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