Qlikview - Filtering 100s of Supplier Data based on Filter Column Values in a gsheet.

[Vishal]

Hello Community, 

Please help me - 

I have data for around 300 suppliers. I am fetching through SQL query, union all supplier data. It has about 15 columns, like Supplier Name, Address, City, State, Zip, product, Purchases, Sales, Region, Branch, Sender.... 

Now I need to filter a few columns out of these, like first on Address, then on City, State, Sender. I have a separate google sheet for it.  For some suppliers, any of these column could be blank, which means, filter should not be applied on that column for that particular supplier, and main data in the main should stay as it is. 

I am stuck around handling the null filter values for the suppliers. I cannot hard code it with where condition, as the filters are dynamic, it changes every month. 

Please guide. 

[Qrishna]

provide some sample data for both SQL data and excel. if they are confidential you can just recreate the the scenario with some dummy data.

[stevedark]

Hi @Vishal 

This sounds like you want to be using a WHERE EXISTS statement.

If the fields in your Google sheet, with the filters in, have the same name then the syntax is simply:

WHERE EXISTS (FieldName)

If they have different names then you need:

WHERE EXISTS (FilterField, LoadingField)

You can have multiple of these tied with an AND if required.

So, you would have something like:

tmpFilters:
LOAD
   [Supplier Name Filter],
   [City Filter],
   [State Filter]
FROM [lib://YourGoogleConnection/YourGoogleSheet] 
;

SQLData:
LOAD
   *
   WHERE EXISTS ([Supplier Name Filter], [Supplier Name])
     OR EXISTS ([City Filter], City)
     OR EXISTS ([State Filter], State)
   ;
SQL SELECT
   *
FROM your.db.SQLTable;

DROP TABLE tmpFilters;

The downside of this approach is that you are pulling all of the data from SQL to then drop it with a WHERE statement. If speed and performance is not an issue then that is the simplest way. Or, if you have a QVD layer where you get all data from SQL and persist to QVD then just load some of the data from the QVD that approach also works well.

If you want to only get the appropriate rows from the SQL table you will need to use variables to build your SQL statement from the filter sheet. Take a look for information on the PEEK statement for how you might do that.

Hope that helps,

Steve

[Vishal]

Thank you @stevedark , for your response. 

I see one problem in this, since SQL data consist numerous suppliers data, and in a scenario wherein Supplier23 doesn't have any filter value for Address, which means Address filter should not be applied on the Suppler23 and its original Address values should stay as it is. However, if I use the Exists() function then it would skip the Supplier23 data entirely. 

There are suppliers whose data is not required to be filtered at all, hence it wouldn't have any column values in the filter table. I hope I am making sense. 

Thanks  @Qrishna , I will try to share a dummy data. 

[marcus_sommer]

From a pure logical point of view it's regardless if n fields have certain values or if they are NULL. Both could be queried in n (nested) combinations of AND/OR/XOR - you need just to apply all relevant ones in the correct combination + order + syntax.

In your case it might be not really a trivial task - and it should lead to the question if it's sensible to resolve the issue with such complex filtering or if it's not more suitable to implement a data-quality approach in beforehand to simplify the afterwards measurements.

This might include a replacement of the NULL, maybe with a simple coalesce(Field, 'no data') or with more sophisticated methods and also checks against the entire record like rangenullcount() and/or with numeric replacements which could be evaluated per sum/max/avg. So it's mainly a question of a data-quality handling ...

[stevedark]

Hi @Vishal 

It sounds like you do need to build the WHERE statement for the SQL dynamically from your filter table.

Something like:

let sWhere = '';

tmpFilters:
LOAD
   [Supplier Name Filter],
   [City Filter],
   [State Filter]
FROM [lib://YourGoogleConnection/YourGoogleSheet] 
;

for iSup = 0 to NoOfRows('tmpFilters') -1
   let sSup = peek('Supplier Name Filter',iSup,'tmpFilters');
   let sCity = peek('City Filter', iSup, 'tmpFilters');
   let sState = peek('State Filter', iSup, 'tmpFilters');

   let sWhere = sWhere & if(sWhere = '', 'WHERE ' , ' AND ');

   let sWhere = sWhere & '("Supplier Name" <> ' & chr(39) & '$(sSup)' & chr(39) & ' OR City = ' & chr(39) & '$(sCity)' & chr(39) & ' OR State = ' & chr(39) & '$(sState)' & chr(39) & ')';

next

DROP TABLE tmpFilters;

// Check the string we have built
TRACE WHERE: $(sWhere);

SQLData:
LOAD
   *
   ;
SQL SELECT
   *
FROM your.db.SQLTable
$(sWhere)
;

You will end up with a long and messy SQL WHERE statement, and what I have there may not work as SQL, but if you can build the SQL you need then it will work.

I suspect some further IF statements will be required in the build of the statement to deal with blank values in your table.

It will be trickier to create the code for this than using the WHERE EXISTS, but once it is done it will be more efficient as you won't be pulling rows from SQL that you don' need.

Good luck!

Steve