Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
kfahri342
Contributor III
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Then you can try this.

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

From xyz;

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this.

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

     Load Text(Field) as Field

     From xyz;

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
kfahri342
Contributor III
Contributor III
Author

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.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Then you can try this.

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

From xyz;

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
kfahri342
Contributor III
Contributor III
Author

Works perfectly. Thank You

Not applicable

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!