Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
oliveton
Contributor

Remove Numbers in Script Based on Conditions

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 !!

1 Solution

Accepted Solutions
Highlighted
YoussefBelloum
Esteemed Contributor

Re: Remove Numbers in Script Based on Conditions

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) )

View solution in original post

14 Replies
Highlighted
YoussefBelloum
Esteemed Contributor

Re: Remove Numbers in Script Based on Conditions

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) )

View solution in original post

Highlighted
oliveton
Contributor

Re: Remove Numbers in Script Based on Conditions

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?

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Remove Numbers in Script Based on Conditions

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

Highlighted

Re: Remove Numbers in Script Based on Conditions

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;

Highlighted
oliveton
Contributor

Re: Remove Numbers in Script Based on Conditions

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!

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Remove Numbers in Script Based on Conditions

‌you’re welcome good luck

Highlighted
oliveton
Contributor

Re: Remove Numbers in Script Based on Conditions

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.

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Remove Numbers in Script Based on Conditions

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

Highlighted
oliveton
Contributor

Re: Remove Numbers in Script Based on Conditions

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