Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am having a very strange problem with my QV (Orders/FC report) as I have found that a handful of part numbers (300 out of 45000) are not coming through. The report contains multiple QVD tables combined with several Excel tables.
The excel tables are joined with the primary table QVD (This Year Last year orders - TYLY). The other year QVD's provide order data for prior year orders (i.e. 2016, 2015 etc..) where the delivery date for the order is >2017 . These are all concatenated with the main table (TYLY). In each QVD table I have a "Where" clause to point to our specific market (i.e. Where (Market = 'gum' ) or (Market = 'soda') so I am only pulling data that is pertinent to our business and reduces size. On the plus side, I have no synthetic keys.
I compared my data result to our IT benchmark report and found there are about 300 part numbers missing from my Orders/FC report that are present in the IT published report. When I rebuilt a test report with only the "TYLY" orders QVD using the identical script as my orders/FC working file, the 300 part numbers are present and orders data matches the benchmark IT report perfectly.
To further test as I suspected maybe the concatenate/joining of the other tables could be the issue. So I stripped out each table one at a time from my Orders/FC report and then perform a complete reload each time - with the same result (missing part numbers). After removing all but the "TYLY" table (identical now to my test report), it still is missing these same part numbers?
I am totally baffled at what is happening here as my expectation is that two reports with identical scripts should produce the same result - it goes against the grain of logic but am still new to this. I am not in a position to rebuild the entire report given a deadline and I have spent a great deal of time building formulas, formatting and validating data that will take weeks to get back to. I don't know where to go from here in terms of troubleshooting - so any guidance on what could happening and what I could do to fix would be greatly appreciated!
Perhaps I should ask this question a little differently. Does QlikView retain a type of "memory" of prior reloads that could exclude data - is there a way to clear this?
My original load of the TYLY orders data contained all the missing part numbers (i.e. data matched my benchmark). After adding the other tables in my script, the part numbers were no longer in my report. I then removed these additional tables (only TYLY remained), reloaded but yet the part numbers are still missing?
TYLY -Original load:
1a
1b
2a
2c
3a
3x
TYLY + new tables
1a
1b
2a
2c
3a
3x
TYLY (removed new tables)
1a
1b
2a
2c
3a
3x
After much pain - I finally figured out that by removing all of the "joins" from my Excel tables in the script, the data finally appeared in my report! I then added back the respective join (after careful examination/review) and data is now correct in the report. At this point I cannot point to a specific solution (I.e. this table, join was root cause), but only know through this process of removing all of them and then adding back in by one (each after full reload to verify) by one, the problem is resolved. I spent a great deal of time to carefully examine each table and how this would link to the others. If I am able to point to specific condition that caused this I will give an update.