Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
poluvidyasagar
Creator II
Creator II

Expression for calculated Dimension?

Hi,

I am trying to achieve the following:

My Data:

PartMasterTable:

PartCategoryDay1Day2Day3Day4Use Location
40179255A20151515G50021
40179255A20151515G53123
40179255A20151515H13212
61697461B10102015G53123
61697461B10102015G53423
61697461B10102015G53124

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:

  

PartUse LocationType
40179255G50021Shared
40179255G53123Shared
40179255H13212Shared
61697461G53123G
61697461G53423G
61697461G53124G

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

1 Solution

Accepted Solutions
sunny_talwar

Try this

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


Capture.PNG

View solution in original post

7 Replies
sunny_talwar

Try this

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


Capture.PNG

View solution in original post

trdandamudi
Master II
Master II

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

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

poluvidyasagar
Creator II
Creator II
Author

Thanks Sunny.. It worked

poluvidyasagar
Creator II
Creator II
Author

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
Champion III
Champion III

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
Creator II
Creator II
Author

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