Differentiate field values

[ba11]

Hi All,

I have a table field (REQUIREMENT.DIMENSION) with both whole number and fraction values, if I only want to use the whole number values, how do I do that so that it completely ignores the fraction values either in a chart or in the load script?

Thank you.

[rwunderlich]

Load Floor(REQUIREMENT.DIMENSION) as REQUIREMENT.DIMENSION

-Rob

[ba11]

Thanks Rob, would this not round down the fraction values? it's a case of one part having multiple Dimensions but I only want to deal with the values which are not fraction and ignore the others. Thanks

[Ahidhar]

floor() should do the work for you could you share sample data 

[Aditya_Chitale]

If you want to exclude fraction values from the data itself, you can use where condition like:

Table:
load
  ....
  ...
From [XYZ.qvd] where substringcount(FieldName , '.') = 0 ;

Regards,

Aditya

[steeefan]

Maybe flagging the values could work:

LOAD
  (Floor(REQUIREMENT.DIMENSION) = REQUIREMENT.DIMENSION) AS isInt,
  REQUIREMENT.DIMENSION,
  ...
FROM
  [lib://file.qvd]
  (qvd);

Then use Set Analysis to only get these values.

[tresesco]

May be like:

Where Frac(REQUIREMENT.DIMENSION) =0 ;

[ba11]

There are multiple corresponding Dimensions for every BaseID, what I am wanting to do is call only the BaseID where the Dimensions are whole numbers. 

Find below

[steeefan]

Same code as above or, based on the format you have, reversed:

NOCONCATENATE LOAD
  REQ.DIM,
  (Floor(REQ.DIM) = REQ.DIM) AS isInt,
  SubStringCount(REQ.DIM, '/') > 0 AS isFraction
RESIDENT
  Data;

[Aditya_Chitale · Accepted solution]

You can restrict those values from script itself. Just change substringcount() as below:

Table:
load
  ....
  ...
From [XYZ.qvd] where substringcount(FieldName , '/') = 0 ;

Regards,

Aditya