Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Then you can try this.
Load If(Isnum(Field),Date(Field,'MMM-YY'),Field) as Field
From xyz;
Regards,
Kaushik Solanki
Hi,
Try this.
While loading the data load in text only. Like below.
Load Text(Field) as Field
From xyz;
Regards,
Kaushik Solanki
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.
Hi,
Then you can try this.
Load If(Isnum(Field),Date(Field,'MMM-YY'),Field) as Field
From xyz;
Regards,
Kaushik Solanki
Works perfectly. Thank You
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
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