topic Re: Date & Text Issue on Pivot Table in QlikView

Date & Text Issue on Pivot Table

kfahri342 — Fri, 11 Aug 2017 15:49:11 GMT

Hi,

I have the following underlying table

Type A
Dec-17
Mar-18
TBC
Issue Raised

When I put this in QlikView, it looks like:

Type A
43070
43160
TBC
Issue Raised

I have tried the following formula to get the date in "MMM-YY" format; =Date([Type A],'MMM-YY') .

But it will then get rid of my Text. Which again is not what I am looking to achieve. Please also note, the Text may vary and change over time, so a nested IF function such as, =IF([Type A]=TBC,'TBC',(Date([Type A],'MMM-YY'))) , is not ideal.

Any suggestions please.

Many Thanks

Re: Date & Text Issue on Pivot Table

kaushiknsolanki — Fri, 11 Aug 2017 15:59:25 GMT

Hi,

Try this.

While loading the data load in text only. Like below.

Load Text(Field) as Field

From xyz;

Regards,

Kaushik Solanki

Re: Date & Text Issue on Pivot Table

kfahri342 — Fri, 11 Aug 2017 16:02:27 GMT

Thanks for the suggestion. But I ideally want to keep them in date format. As I sort these dates from earliest to latest. If I change these into dates, they will be ordered alphabetically.

Re: Date & Text Issue on Pivot Table

kaushiknsolanki — Fri, 11 Aug 2017 16:10:23 GMT

Hi,

Then you can try this.

Load If(Isnum(Field),Date(Field,'MMM-YY'),Field) as Field

From xyz;

Regards,

Kaushik Solanki

Re: Date & Text Issue on Pivot Table

kfahri342 — Fri, 11 Aug 2017 16:15:23 GMT

Works perfectly. Thank You

Re: Date & Text Issue on Pivot Table

Fri, 20 Oct 2017 13:41:53 GMT

Hi, I am new to qlikview and this community,

I have a question about your reply concerning the Pivot table date and text.

In the answer you provided to Kozan Fahri, does the word Field refer to the col name or field from the date? And what would xyz be representing?

Thank you in advance!

debra

Re: Date & Text Issue on Pivot Table

kaushiknsolanki — Mon, 23 Oct 2017 05:27:53 GMT

Hi,

Yes the Field is the Column name from the table. And XYZ is the table from which this field belongs.

The given is the code for the scripting, but you can use the below code as calculated dimension in your pivot table.

If(Isnum(Field),Date(Field,'MMM-YY'),Field)

Regards,

Kaushik Solanki