Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
kfahri342
New Contributor III

Date & Text Issue on Pivot Table

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

1 Solution

Accepted Solutions

Re: Date & Text Issue on Pivot Table

Hi,

Then you can try this.

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

From xyz;

Regards,

Kaushik Solanki

6 Replies

Re: Date & Text Issue on Pivot Table

Hi,

Try this.

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

     Load Text(Field) as Field

     From xyz;

Regards,

Kaushik Solanki

Highlighted
kfahri342
New Contributor III

Re: Date & Text Issue on Pivot Table

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

Hi,

Then you can try this.

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

From xyz;

Regards,

Kaushik Solanki

kfahri342
New Contributor III

Re: Date & Text Issue on Pivot Table

Works perfectly. Thank You

Not applicable

Re: Date & Text Issue on Pivot Table

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

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