Remove Numbers in Script Based on Conditions

[oliveton]

Hello,

I want to remove all part numbers that are a letter "T" or "P" under "PopCnd" that have no description under "PartStatusTwo." I want to do this in the script so they just don't show up at all.

If possible I would also want to do this for all part numbers that have no description under "PartStatusTwo" and are a "0" or "1" under "LC."

I've tried flagging these numbers as "REMOVE" in my expression to let myself know to take these out after exporting to excel, but I'm afraid I'm creating the world's largest IF statement and I know there's got to be a better way.

Thank you !!

[YoussefBelloum · Accepted solution]

Hi,

try this as a WHERE clause..

.

FROM...

Where NOT ( (PopCnd='T' and len(trim(PartStatusTwo))=0) or (PopCnd<>'P'  and len(trim(PartStatusTwo))=0) )

[oliveton]

It keeps saying that "PartStatusTwo" can't be found......could it be because PopCnd and PartStatusTwo come from 2 separate files in the load? Or does that not make a difference?

[YoussefBelloum]

it makes a difference if you make some test in a WHERE if the column is not loaded yet.

you can finish to load all the fields you need and make an extra load (using resident) on which you put the WHERE described above

[rubenmarin]

Hi Natalie, if they come from different files you have to join in the same table or use a mapping or exists(), ie:

// Load %Cram with description in PartStatusTwo

chkPartStatusTwo:

LOAD %Cram as chkPartStatusTwo

Resident PartStatusTwoTableName // PartStatusTwo should be loaded before this table

Where Len(Trim(PartStatusTwo))>0;

TableWhitPopCnd:

LOAD Field

From ...

Where not Match(PopCnd, 'T', 'P') // Load all rows with other types

          or Exists('chkPartStatusTwo', %Cram); // and the T,P with PartStatusTwo Lenght>0

DROP table chkPartStatusTwo;

[oliveton]

OK thanks so much guys, I'm super new to this and kind of teaching myself so not going to make you wait while I mess with this! But it sounds like this should work. Going to mark Youssef as right, since that was the initial question. Thanks again!

[YoussefBelloum]

‌you’re welcome good luck

[oliveton]

I am doing something wrong because it keeps saying my fields can't be found when trying to load. Will one of you please help me out possibly I'm not even sure if I put everything in the right place but there's no error in the script anyway.

[YoussefBelloum]

because you're making the test on the Alias field name, you should make it on the original field name which is loaded during that load:

use [PART STATUS II] on the WHERE

[oliveton]

It still says the field can't be found for some reason, thanks though. I tried this yesterday too.