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: 
hammermill21
Creator III
Creator III

Separating Data

Hello everyone,

I have data that I am separating into two other columns which I got working:

Capture.PNG

So I take Failure Mode and break it down into WorkOrder and Reason by adding this to the script:

[Failure Mode] AS [Failure Mode],

    subfield([Failure Mode],':',1) AS WorkOrder,

    subfield([Failure Mode],':',-1) AS Reason,

But for WorkOrder I only want it to show the WorkOrder number if the failure mode has one. How can I do that?

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

Not sure if the Reason will ever have numbers... because if it does... then you can still see numbers from Reason show up for WorkOrder... may be try this

KeepChar(If(Index([Failure Mode], ':') > 0, Subfield([Failure Mode],':',1)), '0123456789') as WorkOrder,

View solution in original post

11 Replies
sunny_talwar

Try this

If(Index([Failure Mode], ':') > 0, Subfield([Failure Mode],':',1)) AS WorkOrder,

YoussefBelloum
Champion
Champion

Hi,

try this:

    KeepChar(subfield([Failure Mode],':',1),'123456789') AS WorkOrder,

hammermill21
Creator III
Creator III
Author

Hi,

thank you this worked perfectly, I just added a 0 to the '123456789'.

Thanks!!!

hammermill21
Creator III
Creator III
Author

Sunny,

This also worked but I used the other one so it removes the "WO#" for the text also.

THANK YOU!!

sunny_talwar

Not sure if the Reason will ever have numbers... because if it does... then you can still see numbers from Reason show up for WorkOrder... may be try this

KeepChar(If(Index([Failure Mode], ':') > 0, Subfield([Failure Mode],':',1)), '0123456789') as WorkOrder,

YoussefBelloum
Champion
Champion

You're welcome ! you're just wrong on the correct answer

hammermill21
Creator III
Creator III
Author

Oh I see what you are saying. Ok I tried that and it works but randomly sometimes the reason it's showing:

Capture.PNG

Or it does this:

Capture.PNG

Seems strange since the main failure mode doesn't have those numbers?!

sunny_talwar

We only proposed solution for WorkOrder column... did you make changes to Reason also?

sunny_talwar

For Reason, I would just try this

SubField(':' & [Failure Mode], ':', -1) as Reason,