topic Re: QlikView and Full Stops. in QlikView

QlikView and Full Stops.

Tue, 21 Apr 2015 10:13:37 GMT

Hello,

I currently have two different orders and for this instance we will call it Order 123 and there is another order in the system with Order No 123. (with a full stop at the end). However, when I look in QlikView it is showing that there are two orders for 123 when in fact there aren't due to the full stop. Is there a way of getting around this?

Thanks in advance.

Andrew

Re: QlikView and Full Stops.

anbu1984 — Tue, 21 Apr 2015 10:20:33 GMT

Remove the dot while loading Order No in script like below

Load Purgechar(OrderNoField,'.') As OrderNoField ...

Re: QlikView and Full Stops.

swuehl — Tue, 21 Apr 2015 10:20:54 GMT

If these two records belong to the same order, I would recommend removing the full stop on load time:

LOAD

purgechar(OrderField,'.') as OrderField,

...

Re: QlikView and Full Stops.

Tue, 21 Apr 2015 10:21:32 GMT

while loading you can change the Order nos like

=PurgeChar('Order123.','.')

This will return Order123, the trailing fullstop is removed.

Re: QlikView and Full Stops.

stigchel — Tue, 21 Apr 2015 10:22:48 GMT

In the load script force the order field to load as text:

Text(Order) as Order

Text

text(expression )

The text function forces the expression to be treated as text, even if a numeric interpretation is possible.

Re: QlikView and Full Stops.

Tue, 21 Apr 2015 10:31:27 GMT

I've just tried using Piet Hein van der Stigchel's and it hasn't worked.It is still bringing back the result as one, when it should be two. These are separate orders as well, so the PurgeChar function wouldn't work here (anbu cheliyan swuehl and ramko496‌)

Re: QlikView and Full Stops.

jagan — Tue, 21 Apr 2015 10:39:04 GMT

Hi,

Try

Text(Replace(OrderNumber, '.', ''))

Hope this helps you.

Regards,

Jagan.

Re: QlikView and Full Stops.

PrashantSangle — Tue, 21 Apr 2015 10:40:38 GMT

Hi,

Can you explain your requirement with examples.???

When you need two different orders and when you required as one.

Regards

Re: QlikView and Full Stops.

Tue, 21 Apr 2015 10:43:58 GMT

Hello.

QlikView currently shows Order 123 with a frequency of two.

However, when I look in the database there is Order 123 (which QlikView shows) and Order 123. (which QlikView doesn't show because of the full stop.)

What I require is the two to be shown differently, ideally as they are in the database.

Kind Regards

Andrew

Re: QlikView and Full Stops.

jonathandienst — Tue, 21 Apr 2015 10:44:43 GMT

Piet's suggestion is correct and it should work. I suggest that you post the relevant part of your load script to see why it appears not to be working for you.

Jonathan

Re: QlikView and Full Stops.

Tue, 21 Apr 2015 10:48:09 GMT

Okay here goes.

LOAD

ORDER_NO&'_'&SITE as ORDER_KEY;

LOAD

Text(ORDER_NO) as ORDER_NO,

SITE

FROM database_table.qvd (qvd)

Re: QlikView and Full Stops.

jonathandienst — Tue, 21 Apr 2015 10:56:51 GMT

Script:

LOAD Text(test) As test Inline

[

test

123

123.

0123

];

Output in list box with frequencies:

Re: QlikView and Full Stops.

awhitfield — Tue, 21 Apr 2015 10:57:38 GMT

Hi Andrew,

the example attached is as per Stigchel‌ reply, just loading from a simple csv you end up with both 123 and 123.

What are you loading from, can you supply the SELECT part of the script? Is the . getting stripped out before it gets into QV?

Regards

Andy

Re: QlikView and Full Stops.

jonathandienst — Tue, 21 Apr 2015 10:59:09 GMT

I expect the problem already exists in the qvd. The Text() needs to be in the original load from the database (in the qvd generator).

HTH

Jonathan

Re: QlikView and Full Stops.

jpenuliar — Tue, 21 Apr 2015 10:59:49 GMT

Tell me, do you bleed? You will!

Re: QlikView and Full Stops.

Tue, 21 Apr 2015 11:03:11 GMT

Hi,

I'm loading straight from the database. It is Oracle, would that make a difference? This then gets put into the .qvd file.

Kind Regards

Andrew

Re: QlikView and Full Stops.

awhitfield — Tue, 21 Apr 2015 11:08:55 GMT

"That's how it starts. The feeling, the powerlessness… that turns good men cruel"

Re: QlikView and Full Stops.

awhitfield — Tue, 21 Apr 2015 11:31:53 GMT

Hi Andrew,

unfortunately I have no oracle knowledge at all, but if you execute the select statement in Oracle, does it actaull return the 123. ?