Basic data comparison question

[#1]

Hi Guys

I've been trying to do this for a couple of days and just not been able to...

Its a pretty basic job: I have two tables, one with raw sales data (call it RawSales), one with processed sales data ( ExcelSales). Some sales are legitimately excluded in the final data set. I want to check that all sales records from RawSales are present in the FinalSales table, apart from any exclusions. If any are missing, I want to show those records in a table to the user.

Each record has a unique id which i can use to join/compare on, and I have made sure that all no other fields have the same name in the joining tables.

The method I am using is:

JointTable:

Load [column list], RawTransactionID as TransactionID resident RawSales; left join load [column list], ExcelTransactionID as TransactionID resident ExcelSales;

Then I create another resident table, where:

IssueSales:

load [columnlist] resident JointTable where isnull(ExcelProductCode) =1 and JointProductCode <> 'Excluded';

I am assuming that joining behaviour is similar to SQL Server. However this does not give me any records in the IssueSales table, even though I know that there are missing records in ExcelSales.

Am I doing something wrong? And is there a better way to do what I am trying to do?

Thanks for any help!

Jay.

[Oleg_Troyansky · Accepted solution]

Hi Jay,

1. Try replacing isnull() = 1 by the following:

len(trim(ExcelProductCode)) = 0

sometimes isnull() behaves funny... Besides, it's probably wrong to compare the result of the Boolean function to 1, because true= negative 1 in QlikView. It would be enough to state where isnull() and ...

2. For troubleshooting purposes, count rows in your JointTable before the left join and after the left join. Sometimes, depending on the nature of the data, you might get "surprising" results.

good luck!

Oleg

[#3]

Thanks Oleg, I'll try that.

Incidentally, is there a way to just run bits of a script without reloading all the data? In this case, the change is on the last table but to check if it works, I'll have to reload the previous two tables, which are pretty big.

I am using a reduced data set for testing but it would be handy to be able to do that.

[eiconsulting]

Sorry it's too late for me to make a complete model to test it. But when you have a table with all the data wouldn't be simpler to just make a graph (table) to list the records where your condition is met? Why create another table in the script?

I mean you make a column with an expression that is 1 if the condition is met and show only those lines. You can also make a conditional formula that only shows a line if the condition is met like if (condition, 1) all the rest will be hidden.

Should it not work if you show the full table you would be able to catch at least one record to see what's wrong .

Flavio

[#5]

That should work as well Flavio, thanks. I'm just not sure if my left join is doing the job. I'll try both suggestions now and post back in an hour or so.

[#6]

Hi Oleg,

THanks - my comparing to 1 was the problem. both isnull() by itself and len(trim()) = 0 worked.

[#7]

Hi Flavio

My single resident table is made by joining the other two tables. How would I introduce an extra column in there?

I tried creating a table object and putting the data from the JointTable in there, and using the calculation condition to select for isnull(ExcelDistiName) but that didnt seem to filter the dataset correctly....

[#8]

Ah, sorry I see what you mean. Yep, that worked as well! 🙂