Unable to filter out one single item - plz help!

[datanibbler]

Hello,

I have an app to calculate the usage of our warehouse - similar to one I already built for another one.

In the underlying Excel "database" where all the BINs and storage_spaces that can be used in the company_database are listed, there is one sheet >>Ausfiltern<< which means that all the BINs in the live_database which are not to be considered (because they do not represent a physical storage_space) are listed on that sheet.

=> Those are then loaded into a table

=> Using a WHERE NOT(EXISTS()) clause in the primary LOAD from the live_database, I filter out all the BINs listed on that sheet.

So far, that always works

<=> There is one BIN just called >>1<< which I cannot filter out and I don't understand why.

According to an ISNUM() query, it is numeric.

I have it listed on that sheet, of course, and it is properly loaded into the table in QlikView that I use for all those.

<=> Somehow it "survives" that clause and it is still in the table from the live_database.

=> Then I just added another specific clause >> AND [fieldname] <> 1 << or >> AND [fieldname] <> '1' << (I tried both)

<=> Somehow, that one BIN survives even those and it is still in the table.

Any ideas?

Thanks a lot!

Best regards,

DataNibbler

[MK_QSL]

Are all BINs in number format?

Are those in live_database are in number format?

Hope that 1 in no physical bin and 1 in live_database is having same format.. plus no additional spaces etc.

Can you check Length of BIN 1 in both places?

[datanibbler]

Hi Manish,

no, most BINs are in text format and have a more explanatory name than >>1<< 😉

I will try with TRIM next and report back if it works or not.

[datanibbler]

Hmm ... both (in the table I use to filter out those BINs which I do not want) and in the live_database, that BIN is numeric and has a length of 1.

Also, I can display both that table and the live_database (they are not linked), and still I see that BIN ...

I keep on trying with different combinations, but I would be grateful if you have any more ideas?

Thanks a lot!

Best regards,

DataNibbler

[MK_QSL]

Can you provide your script here?

Just want to check...!

[datanibbler]

Hi Manish,

I can do so, but for now the BIN is gone all of a sudden - so either my script has suddenly changed behaviour and it is now filtered out or it is just not being used anymore ..

[datanibbler]

Ah - now I know it - or, I know what is the matter, I don't know why yet ...

Seemingly, that BIN >>1<< is not in the LOAD from the live_database - then comes a series of APPLYMAP() commands where I map several BINs to one a number of times wherever there are several BINs which correspond to just one physical area because these of course have just one capacity.

=> and after that series of APPLYMAP() commands, that BIN >>1<< is there.

I don't know which part of my script exactly you need now? I guess the part where i create all those mapping_tables (incl. the underlying Excel file) and the part with all the APPLYMAP() commands.

I will attach those now.

Best regards,

DataNibbler

[MK_QSL]

Why are you loading all Mapping load separately ?

Also so many mapping loads?

Can you try this?

For each vSheet in '(QC)REC','UMPA', 'SCHRINOUT', 'GTBO', 'QS', 'Soli'

       Map_Table:

     Mapping  LOAD BIN_Name,

      Fläche

  FROM

  Zusammenfassungen_Flächen.xlsx

  (ooxml, embedded labels, table is [$(vSheet)]);

Next vSheet

Now use only ApplyMap once..

Try this... let me know!

[datanibbler]

Hi Manish,

well, it works - and for the time being, that  BIN is gone. Let's wait till tomorrow and see if it stays gone ...