Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
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
Can you provide your script here?
Just want to check...!
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 ..
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
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!
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 ...