topic Re: Number Formatting Issue in QlikView

Number Formatting Issue

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am currently working on an excel sheet comparing numbers that are assigned to different field values in order to create logical data. The numbers are typically formatted as ex. 2938 or 20098. There are a couple of numbers that contain a R or a P at the end indicating that they are in rework or if they are a project. In the script I have tried using Num(Num#([WR #], '#####','R')) in the script. [WR #] is the field that contains those numbers. Without this, I cannot see a number like 20920R or 1097P. Using the Num function, I am able to at least see the number itself, but it does not contain the R or P, which is critical to my data. If anyone knows how to fix this, it would be very helpful.

Thanks!

Re: Number Formatting Issue

sunny_talwar — Tue, 14 Jun 2016 20:06:29 GMT

What if you just use this:

Num#([WR #], '#####','R')

Re: Number Formatting Issue

sunny_talwar — Tue, 14 Jun 2016 20:07:10 GMT

Alternatively, try this:

Alt(Num#([WR #], '#####'), Num#([WR #], '#####R'), Num#([WR #], '#####P'))

Re: Number Formatting Issue

Tue, 14 Jun 2016 20:14:44 GMT

The number is displaying, but the letter is still not appearing.

Re: Number Formatting Issue

sunny_talwar — Tue, 14 Jun 2016 20:31:54 GMT

If you are looking this in a table box object, can you make sure that you have not set the number format to be numeric? Can you change it to expression default and see if that works?

Re: Number Formatting Issue

Tue, 14 Jun 2016 20:43:12 GMT

It is not set to numeric format, it is just in its default format. If this number is the link between multiple tables inside the script, would I have to put the num function in front of all of them? For example, I have a table called [Number] and a table called [Request] in the same tab that are connected by the field 'ID'.

QUALIFY *;

UNQUALIFY [ID];

Number:

Load Num#([WR #], '#####','R') as ID,

...

From ...

QUALIFY *;

UNQUALIFY [ID];

Request:

LEFT JOIN Number

Load [Request Number] as ID,

...

From ...

Re: Number Formatting Issue

rwunderlich — Tue, 14 Jun 2016 23:20:39 GMT

Try this in the script:

Dual([WR #], keepchar([WR #],'0123456789')) as [WR #]

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Number Formatting Issue

swuehl — Tue, 14 Jun 2016 23:32:42 GMT

There might be an issue if you are having field values like 2938 and 2938P and you expect them to link on (or be a text representation of) the same numeric value, 2938.

For any given numeric value, QV will store at most a single text representation, the one the comes first in load order, I believe.

Re: Number Formatting Issue

Wed, 15 Jun 2016 12:04:36 GMT

Still not getting the letter to show. I've even tried putting it on every [WR #] field and it won't work properly either.

Re: Number Formatting Issue

swuehl — Wed, 15 Jun 2016 13:44:44 GMT

Would you be able to create a small sample QVW (could contain mock up /scrambled data for all columns except [WR #]) and post it to this thread?

edit: well, this probably won't show the original data, so maybe it's better if you could provide sample input files for [WR #]?

Re: Number Formatting Issue

Wed, 15 Jun 2016 17:18:28 GMT

Hope this helps.The other tabs in my script come from other excel files that I do not have access to, but this is what my excel sheet looks like simplified and using simplistic data.

Re: Number Formatting Issue

swuehl — Wed, 15 Jun 2016 17:44:56 GMT

Rob's suggestion seems to work for me - with your sample data:

LOAD Dual([WR #],PurgeChar([WR #],'PR')) as Test,

LOAD [WR #],

Developer,

[Date Assigned],

[Estimated Project Hours],

[Actual Hours Spent]

FROM

(ooxml, embedded labels, table is Sheet1);

If this is not working for you, I guess that's due to the other data source you are loading.

Could you outline how your script is loading in your [WR #] records?

Have you considered what I mentioned above about the text representation of dual values being limited to the first representation of a value encountered during LOAD?

Re: Number Formatting Issue

Wed, 15 Jun 2016 18:10:49 GMT

Sorry, I am new to Qlikview, but I don't think dual is the right thing to use in this situation. I am already left joining one table to another. Both WR and WSR have the same WR IDs, but WSR has additional information that I need to join to WR. Both of these WR ID fields have numbers like 2134,21134,1231R,12452R,2142P and 21234P. I need all of the WR ID to show both the number and the letter at the end.

QUALIFY *;

UNQUALIFY [WR ID];

WR:

LOAD

[Request Number] AS [WR ID],

Status,

[Assigned Date]

. . .

From

;

QUALIFY *;

UNQUALIFY [WR ID], [Assigned Date],

WSR:

Left JOIN ([WR])

LOAD

Num#([WR #], '#####','R') as [WR ID],

Developer,

[Date Assigned],

[Estimated Project Hours],

[Actual Hours Spent]

. . .

From

;

Re: Number Formatting Issue

swuehl — Wed, 15 Jun 2016 18:26:54 GMT

Just to clarify, you may also use dual values without using dual() function:

Data Types in QlikView

Have you tried just loading in your values as they are? or using text() function?

Re: Number Formatting Issue

Wed, 15 Jun 2016 18:36:36 GMT

When I have tried loading them as they are, all of them except the ones with letters appear. That's why I originally did the num function to at least get the number to appear without the letter. I tried the text() function and none of the numbers appeared on my dashboard.

Re: Number Formatting Issue

swuehl — Wed, 15 Jun 2016 19:00:59 GMT

You would need to apply text() to both table loads.

I assume you are trying to join an ID in WSR table like 2134R to and ID in WR table like 2134, is this correct?

If so, do ID 2134 appear next to 2134R in WSR table and you need to handle these differently?

If not, try to purge the letters from your ID field in WSR table and store the additional information into an addtional field:

WSR:

Left JOIN ([WR])

LOAD

Purgechar([WR #], 'PR') as [WR ID],

KeepChar([WR #], 'PR') as [WR ID info],

Developer,

[Date Assigned],

[Estimated Project Hours],

[Actual Hours Spent]

. . .

From

;

Sorry for being still not really sure what you are trying to achieve.

Stefan

Re: Number Formatting Issue

Thu, 16 Jun 2016 12:07:39 GMT

Yes I am trying to join an ID like 2143R in the WSR table to the same ID in the WR table but the one in the WR table does not have an R. I need to handle these numbers as if they are identical. They have the same developer, assigned date, project hours, etc. I see what you are trying to do there in the code, but that unfortunately did not work in the script as well. It got rid of all the numbers except one.

Re: Number Formatting Issue

swuehl — Thu, 16 Jun 2016 12:56:47 GMT

You can only JOIN or link key fields based on matching values.

2143 and 2143R are not same, matching values.

You would need to create a set of matching values based on something like I tried above (you would need to check if values are matching given your data).

If you are using multiple keys for JOIN / linking of tables, all these fields need to match to successfully create your link / join. Maybe your issue is also related to not matching values e.g. in date / timestamp / developer fields.

Re: Number Formatting Issue

swuehl — Thu, 16 Jun 2016 14:21:09 GMT

I would also suggest that you link your tables using the key fields instead of joining them, for debugging puporses.

Then create a table box with the key fields and another field from both tables WSR and WR.

(You can also look at each key field separately first)

No you should see which values match and which don't, this might give you some additional insight why the join doesn't work.

Re: Number Formatting Issue

effinty2112 — Thu, 16 Jun 2016 14:36:22 GMT

Hi Caitlin,

Data:

LOAD

[WR #],

PurgeChar([WR #],'PR') as [Numeric WR #],

KeepChar([WR #],'PR') as [Alpha WR #],

Developer,

[Date Assigned],

[Estimated Project Hours],

[Actual Hours Spent]

FROM

[Sample WR sheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

Will give you:

WR #	Numeric WR #	Alpha WR #	Actual Hours Spent	Date Assigned	Developer	Estimated Project Hours
2451P	2451	P	36	08/01/2015	Bob	34
2458R	2458	R	30	26/02/2014	Jane	22
5922	5922		40	03/12/2011	Paul	45
23648R	23648	R	102	05/06/2016	Bob	80
24514P	24514	P	54	05/11/2015	Jane	68
25478	25478		21	09/08/2013	Paul	15
45784	45784		95	18/03/2015	Paul	105