topic Re: Expression Help in QlikView

Expression Help

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

Hi,

I would appreciate some help with the below expression.

I have the following data:

Vessel Name Act Port

Dan123 L UK

Dan123 D France

I would like to create a table that show the data as:

Vessel Name Load Port Discharge Port

Dan123 UK France

I thought I could use If(Act='L',Port), but it didn't work.

Thanks,

Daniel

Re: Expression Help

Anil_Babu_Samineni — Wed, 14 Feb 2018 15:00:22 GMT

May be create table

Dimension

1) [Vessel Name]

2) Pick(Match(Act, 'L', 'D'), 'Load Port', 'Discharge Port')

Expression

1) Port

Finally, Drag column to Row from second dimension

Re: Expression Help

danielnevitt — Wed, 14 Feb 2018 15:12:56 GMT

Thanks Anil.

I tried the above, however the data appears on two lines. Is it possible to present the data on one line?

Thanks,

Daniel

Re: Expression Help

Anil_Babu_Samineni — Wed, 14 Feb 2018 15:17:20 GMT

Looks like working?

Re: Expression Help

danielnevitt — Wed, 14 Feb 2018 15:21:35 GMT

Are you able to post your example?

Thank you for your help.

Regards,

Daniel

Re: Expression Help

Anil_Babu_Samineni — Wed, 14 Feb 2018 15:23:42 GMT

Why Not? PFA

Re: Expression Help

danielnevitt — Wed, 14 Feb 2018 15:40:23 GMT

Thanks Anil.

That now works perfectly. However I have an issue with the format when I try to add additional fields like voyagenumber, charterer etc

Is this possible with this style of table?

Regards,

Daniel

Re: Expression Help

Anil_Babu_Samineni — Wed, 14 Feb 2018 15:41:48 GMT

Please share the Layout which you currently having?

Re: Expression Help

danielnevitt — Wed, 14 Feb 2018 15:57:01 GMT

Hi Anil,

I have attached the underlying data. The first tab 'Voyages' shows the current format and the 'Required Format' tab is how I would like to present this data.

Thanks,

Daniel

Re: Expression Help

Digvijay_Singh — Wed, 14 Feb 2018 15:59:45 GMT

If your data is more complex then you should use the generic load which is made to solve this kind of problem. It converts field attributes into field name which helps to create tables which you are trying to build.

Table1:

Load * inline [

Vessel Name, Act , Port

Dan123 , L , UK

Dan123 , D , France ];

Generic

Load *

Resident Table1;

Re: Expression Help

Anil_Babu_Samineni — Thu, 15 Feb 2018 13:46:38 GMT

That means, Each field name should store it as Act? Is that you are expecting? Can you explain the use case for your customers?

Re: Expression Help

rubenmarin — Thu, 15 Feb 2018 14:21:33 GMT

Hi Daniel, have you tried?:

Only({<Act={'L'}>} Port)

or "Concat(distinct {<Act={'L'}>} Port, ', ')" if Vessel Name can have more than one port for Act='L'