topic Re: Coallace like formula in QlikView

Coallace like formula

ashishtams — Wed, 17 May 2017 06:36:40 GMT

I have a data like

ID environment Date

1 Prod 23-Apr-17

1 Non Prod -

2 Prod 01-May-17

2 Non Prod -

In the pivot table i have used all the above columns as dimensions.

I have one expression to show counts against this.

I need to have the null date for Non prod to be shown same as prod date for that ID. (if date for non prod is null then show the dat of prod )

Need to write either calculated dimension or an expression.

Don't have option to work this out on DB or Script side.

Any help is appreciated.

Regards

Re: Coallace like formula

Kushal_Chawda — Wed, 17 May 2017 06:57:03 GMT

create calculated dimension for Date

=aggr(MaxString(Date),ID)

Re: Coallace like formula

ahaahaaha — Wed, 17 May 2017 07:02:47 GMT

Hi,

as variant for script in attached file

Regards,

Andrey

Re: Coallace like formula

ahaahaaha — Wed, 17 May 2017 07:40:35 GMT

In attached file example Table chart with variant of your solve. If '-' in field [Date} in your data is NULL, in chart expression fragment Trim(Text(Date))='-' should be replaced by ISNULL(Date)=-1.

In chart expression new field [CalDate] created in script not use. Thus you can solve your problem both at the level of the script and the chart.