Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AKKHAN10
Contributor
Contributor

Best way to pull apart information in one field based on several criteria

Hi

I have a field that contains a string of numbers. I need to be able to pull this field apart based on several different sets of criteria in order to obtain a number that is basically in the middle of the strong.  I have tried to create new fields in the data file that calculates off several "IF" statements nested within each other, but it doesn't seem to be working correctly.  Is there a better way to do this? Should it be done in Measures or Dimensions as opposed to in the data table?  My problem is the extraction is based off of several criteria.  See below summary of the criteria.   Any ideas on how to best go about this????

IF Original field is 31 characters

    THEN if the MID field is equal to 6 characters,

                  THEN take the last 15 characters from right

                  ELSE take the last 9 characters from right

            THEN (from above extract) if the MID field is equal to 6 characters,

                      THEN take the 6 characters from left

                      ELSE take 9 characters from left

ELSE (original field is NOT equal to 31 characters but is 24 characters)

    THEN take the first 9 characters from right

      THEN (from above extract) take 8 characters from the left

Need all results from this extracting into a new field, Dimension or Expression so I can use it to analyze against another field.

Thanks for any help you can give!

Aisha

Labels (4)
1 Reply
JordyWegman
Partner - Master
Partner - Master

Hi,

It would look like this, but I don't know what you mean with your first MID part, what does it mean? What are the conditions?

IF( len([YourField]) = 31,
  IF( What do you want here?, 
       left([YourField],6) & right([YourField],15), 
       left([YourField],9) & right([YourField],9)
    )
     IF(len([YourField] = 24,
       left([YourField],9) & right([YourField],9),null()
)

Jordy

Climber

Work smarter, not harder