topic Re: Remove Numbers in Script Based on Conditions in QlikView

Remove Numbers in Script Based on Conditions

oliveton — Mon, 16 Jul 2018 13:57:58 GMT

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

Re: Remove Numbers in Script Based on Conditions

YoussefBelloum — Mon, 16 Jul 2018 14:09:02 GMT

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

Re: Remove Numbers in Script Based on Conditions

oliveton — Mon, 16 Jul 2018 14:44:01 GMT

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?

Re: Remove Numbers in Script Based on Conditions

YoussefBelloum — Mon, 16 Jul 2018 15:07:21 GMT

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

Re: Remove Numbers in Script Based on Conditions

rubenmarin — Mon, 16 Jul 2018 16:37:13 GMT

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;

Re: Remove Numbers in Script Based on Conditions

oliveton — Mon, 16 Jul 2018 16:48:17 GMT

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!

Re: Remove Numbers in Script Based on Conditions

YoussefBelloum — Mon, 16 Jul 2018 20:45:09 GMT

‌you’re welcome good luck

Re: Remove Numbers in Script Based on Conditions

oliveton — Tue, 17 Jul 2018 12:57:34 GMT

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.

Re: Remove Numbers in Script Based on Conditions

YoussefBelloum — Tue, 17 Jul 2018 13:03:28 GMT

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

Re: Remove Numbers in Script Based on Conditions

oliveton — Tue, 17 Jul 2018 13:19:03 GMT

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

Re: Remove Numbers in Script Based on Conditions

YoussefBelloum — Tue, 17 Jul 2018 13:36:49 GMT

My mistake, I didn't pay enough attention to your code..

you're making a resident load from ths PartStatusTwo table, the field coming from this table is already named PartStatustwo.. so you shoud directly load Partstatustwo and use it on the where.

is it clear for you ?

Re: Remove Numbers in Script Based on Conditions

oliveton — Tue, 17 Jul 2018 14:00:05 GMT

I thought it was but could I possibly see an example?

Re: Remove Numbers in Script Based on Conditions

YoussefBelloum — Tue, 17 Jul 2018 14:03:48 GMT

here is an example:

from the table rangemonths, I already changed the name of the field Months to MONTHS, when I use the old name using the Resident, it will not find it, because I should call it with the last name used via the alias.

Re: Remove Numbers in Script Based on Conditions

oliveton — Tue, 17 Jul 2018 15:00:41 GMT

OK got it, thanks I appreciate it!

Re: Remove Numbers in Script Based on Conditions

YoussefBelloum — Tue, 17 Jul 2018 18:53:50 GMT

‌you’re welcome