Number Formatting Issue

[#1]

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!

[#2]

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.

[swuehl]

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?

[#4]

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

;

[swuehl]

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?

[#6]

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.

[swuehl]

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

[#8]

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.

[swuehl]

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.

[swuehl]

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.

[effinty2112]

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