topic Re: Numbers in a text field in QlikView

Numbers in a text field

Wed, 25 Nov 2020 16:16:04 GMT

I have an Invoice No field that is alphanumeric which has numbers in it. Sometimes the numbers are repeated but to make them unique a +sign has been entered at the end -

53160

53160+

53160-

Qlikview appears to drop the invoice number with the + at the end.

I have tried Text(Inv_No) and left(Inv_No,10) and concating an A at the beginning of the field but having no luck.

Hope someone has some advice please.

Re: Numbers in a text field

vishsaggi — Fri, 25 Aug 2017 19:27:35 GMT

When you say dropping you mean is it not showing in your list box. Can you share a snapshot of your issue?

Re: Numbers in a text field

trdandamudi — Fri, 25 Aug 2017 19:36:32 GMT

I am able to see them:

Data:
Load * Inline [
Numbers
53160
53160+
53160-
];

Result:

Re: Numbers in a text field

Fri, 25 Aug 2017 19:36:33 GMT

But the file in the application shows -

Thanks for getting back so quickly, its the first time I've asked a question.

Re: Numbers in a text field

el_aprendiz111 — Fri, 25 Aug 2017 19:52:47 GMT

Hi,

LOAD *, Invoice &' ' & RecNo() AS option1,
If(Invoice=Previous(Invoice), Peek(Inv2)&chr(43),Invoice) AS Inv2;

LOAD * Inline
[
Invoice
53160
53160
53160
];

Re: Numbers in a text field

Fri, 25 Aug 2017 20:28:51 GMT

Hi,

That retrieved all the records but the invoice field has to be displayed to the user as they would recognise it. The result from your code was -

Is there a further step I can take that will give me back the '+' sign?

Thanks

Re: Numbers in a text field

vishsaggi — Fri, 25 Aug 2017 20:30:25 GMT

Can you trythis in your actual Load script where you pulling this Internal field.

LOAD *,

IF(ISNUM(Internal), Text(Internal), Internal) AS NewInternal;

SQL SELECT ..., Internal

FROM yoursource;

Re: Numbers in a text field

Fri, 25 Aug 2017 20:42:43 GMT

Hi I have stripped out all my code to make sure that isn't having an issue. Your response has left justified all the values but only the 8 records are being retrieved -

Re: Numbers in a text field

JaMajka1 — Fri, 25 Aug 2017 20:45:17 GMT

Hello Donna,

could you copy here your loading script?

what is the source? SQL DB?

BR,

Maria

Re: Numbers in a text field

Fri, 25 Aug 2017 20:52:47 GMT

Hi ,

I have stripped it out completely to try out the above answers and this is the last one I tried-

Invoice:

LOAD *,
IF(ISNUM([INTERNAL NO]), Text([INTERNAL NO]), [INTERNAL NO]) AS NewInternal

FROM
$(vSpectra_QVD_Local)ZBMHSP.qvd
(qvd);

The original data comes from an iSeries.

Re: Numbers in a text field

JaMajka1 — Fri, 25 Aug 2017 21:06:15 GMT

Is the problem already in QVD which is loaded or not?

Re: Numbers in a text field

vishsaggi — Fri, 25 Aug 2017 21:16:03 GMT

Can you check the field [INTERNAL NO] from you actual script.

I mean what ever script you run just check after the reload what values you get for that field. Don't load from QVD.

Re: Numbers in a text field

Fri, 25 Aug 2017 21:29:42 GMT

A selection script to preload our data has been written for us but when you prompted me to go to the tables directly I found the invoices all come in correctly.

Sorry so slow to answer but everything is new.

Thanks for your help.