topic Re: Date (Square) Problem in QlikView

Date (Square) Problem

Anil_Babu_Samineni — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Here, if i want to show date like a square b square like that, How to show that?

Like this,,

Let's Assume if date is 1-Aug-2016 Then i want to show like -- for that image, 1 is instead of a and st is instead of 2 -- 1st-Aug-2016("st" should be acting as square like image)

If 2nd Day -- nd

If 3rd Day -- rd

If 4th --- 30th Day -- th

If 31st Day -- st

Can i know how to achieve that? -- =Date(Now(),'DD' & '^rd' & ' ' & '-' & 'MMM-YYYY')

Re: Date (Square) Problem

pogrebnoymike — Wed, 14 Dec 2016 12:17:11 GMT

Hi Anil,

I believe you would have to write an if statement identifying which ending to use and then use Chr() function with superscript characters. There is a listing of superscript characters here.

Using the Ord() function, you can find the corresponding character number and then use the following:

4ᵗʰ would then be =4&Chr(7511)&Chr(688)

Hope this helps.

Re: Date (Square) Problem

Anil_Babu_Samineni — Thu, 15 Dec 2016 04:35:00 GMT

Kind of helpful this, Would you share the characters list for superscript like

A- Z & a-z like Chr(7511)&Chr(688)

So, May i can look those aSAP

Re: Date (Square) Problem

tamilarasu — Thu, 15 Dec 2016 04:42:27 GMT

Hi Anil,

Check the below link

The Complete List of ALL ASCII Codes and ASCII CHARACTERS

Re: Date (Square) Problem

Anil_Babu_Samineni — Thu, 15 Dec 2016 04:45:36 GMT

Tamil, These are sufficient for me, But this gives only Characters. But i need to get Super characters like above mentioned

Chr(7511) -- "t"

Chr(688) -- "h"

These above are gives upper characters. I am expecting this

Re: Date (Square) Problem

tamilarasu — Thu, 15 Dec 2016 05:03:07 GMT

Anil,

Here you go.

http://www.chem.uky.edu/research/grossman/unicode.html

Re: Date (Square) Problem

Anil_Babu_Samineni — Thu, 15 Dec 2016 05:05:22 GMT

Bit difficult to me, As of now can you help me on this with character numbers

Lower Letters for of these, Can you send Chr values for these. So that, we can discuss another part.

d -

n -

r -

s -

t -

Re: Date (Square) Problem

tamilarasu — Thu, 15 Dec 2016 05:19:01 GMT

It's quite easy Anil. Just use search option (Ctrl + F) and paste the character. You will see the matching letters get highlighted in yellow color. Pick the required one.

I am helping you out with this part.

d - Chr(7496)

n - Chr(8319)

r - Chr(7523)

s - Chr(738)

t - Chr(7511)

Note: I have updated the link.

Re: Date (Square) Problem

Anil_Babu_Samineni — Thu, 15 Dec 2016 05:51:01 GMT

Haha, Yes. But i am using Mobile as of now. Anyhow, I got finally like below

Thanks pogrebnoymike & tamilarasu

Now, Coming to my Rek - I've used below to get some kind of idea. Now, i want to know is there any way to do similar

My Date Format is -- DD-MM-YYYY

If(Left(DateField,2) = '01', Date(DateField,'DD') & Chr(738)&Chr(7511) & ', ' & Date(DateField,'MMM YYYY'),

If(Left(DateField,2) = '02', Date(DateField,'DD') & Chr(8319)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

If(Left(DateField,2) = '03', Date(DateField,'DD') & Chr(691)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

Date(DateField,'DD') & Chr(7511)&Chr(688) & ', ' & Date(DateField,'MMM YYYY'))))

Re: Date (Square) Problem

tamilarasu — Thu, 15 Dec 2016 06:40:58 GMT

Anil,

May be this?

If(Match(Day(DateField),11,12,13) Or (Not Match(Right(Day(DateField),1),1,2,3)), Date(DateField,'DD' & Chr(7511)&Chr(688) & ', MMM YYYY'),

If(Right(Day(DateField),1)=1, Date(DateField,'DD' & Chr(738)&Chr(7511) & ', MMM YYYY'),

If(Right(Day(DateField),1)=2, Date(DateField,'DD' & Chr(8319)&Chr(7496) & ', MMM YYYY'),

If(Right(Day(DateField),1)=3, Date(DateField,'DD' & Chr(691)&Chr(7496) & ', MMM YYYY'),

)))) as FormattedDate

Re: Date (Square) Problem

Anil_Babu_Samineni — Thu, 15 Dec 2016 06:54:28 GMT

Any Short Expression or else we can go this? Can you confirm the same if there is no possibility other expression we can say.

Thanks for your Effort

Update: I had close this discussion, So if anyone has other approach always welcome to gain more knowledge.

Again thanks Tamil Nagaraj

Re: Date (Square) Problem

tamilarasu — Thu, 15 Dec 2016 07:18:59 GMT

I am not sure Anil. Someone else might have better solution than mine. Have a good day.

Re: Date (Square) Problem

Anil_Babu_Samineni — Thu, 15 Dec 2016 09:21:12 GMT

Thanks, But performance is not good while using same scenario. It increase time reload. After that i found one more which is this. I am sure if with SubField not at all good. But time reload taken decreased for 57 Seconds. And I heard Pick Match is more better than If Else Conditions. Do we do same by using Pick Match like Expression2

Expression1:

If(Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '1',Date(DateField,'DD') & Chr(738)&Chr(7511) & ', ' & Date(DateField,'MMM YYYY'),

If(Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '2', Date(DateField,'DD') & Chr(8319)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

If(Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '3', Date(DateField,'DD') & Chr(691)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

Date(DateField,'DD') & Chr(7511)&Chr(688)& ', ' & Date(DateField,'MMM YYYY')))) as DateField1

Expression2:

Pick(Match(DateField,"$(=left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '1')",

"$(=Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '2')",

"$(=Left(DateField,1) <> '1' and Right(SubField(DateField,'-',1),1) = '3')"),

Date(DateField,'DD') & Chr(738)&Chr(7511) & ', ' & Date(DateField,'MMM YYYY'),

Date(DateField,'DD') & Chr(8319)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

Date(DateField,'DD') & Chr(691)&Chr(7496) & ', ' & Date(DateField,'MMM YYYY'),

Date(DateField,'DD') & Chr(7511)&Chr(688)& ', ' & Date(DateField,'MMM YYYY')) as DateField1;

Re: Date (Square) Problem

tamilarasu — Fri, 16 Dec 2016 02:00:17 GMT

Anil, Did you try my suggestion?

Re: Date (Square) Problem

Anil_Babu_Samineni — Fri, 16 Dec 2016 04:06:29 GMT

Tamil, That's working like a charm!! But, It increases the reload time. After that i got some idea related that which i posted on Expression1. So, my intention is to do by using Pick match. Unfortunately, I didn't get it anything. It seems sheet fields / Objects looking Empty on my dashboard.

Re: Date (Square) Problem

tamilarasu — Sat, 17 Dec 2016 09:37:27 GMT

Hey Anil,

Sorry for my late reply. You can use below one.

Pick((Match(Right(Day(DateField),1),1,2,3) + Pick(Match(Day(DateField),11,12,13)+1,0,-1,-2,-3))+1,

Date(DateField,'DD' & Chr(7511)&Chr(688) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(738)&Chr(7511) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(8319)&Chr(7496) & ', MMM YYYY'),

Date(DateField,'DD' & Chr(691)&Chr(7496) & ', MMM YYYY'),

) as FormattedDate

I have attached a sample file for your better understanding. If you still have performance issue, then try replacing day function (I am not sure about it but give a try) with SubField function in the above expression.

Re: Date (Square) Problem

Anil_Babu_Samineni — Sat, 17 Dec 2016 17:34:59 GMT

Sorry for my late reply. You can use below one.

No need to say Sorry Mr. Officer . We are just learners

And thanks some what now i got what i am expecting. And this script decrease few more reload time. Earlier it takes 57 Seconds. But now, 38 Seconds. Thanks again tamilarasu

I may go with this in future level

Re: Date (Square) Problem

tamilarasu — Mon, 19 Dec 2016 03:00:36 GMT

No problem. Have a nice day Anil.

Re: Date (Square) Problem

alis2063 — Mon, 21 Oct 2019 11:45:59 GMT

Hi Guys,

Can we get lists of ASCII conversion as in CHR(xxx) as i am looking data square as below ,

Data