QS Binary load followed by inner join to reduce not working

[leenlart]

Hello, 

I have an existing app (APP1) with the sales for the entire company.  One branch has requested a simplified app for their sales force (NEWAPP).  

I'd like to do a binary load in  NEWAPP followed by a reduction to only keep the data for this branch's salesforce.  

TEST 1 What I've tried : 

Script line 1 : Binary 'address of APP1' ;  

This alone works fine.  I get all the data.

Script line later on: 

INNER JOIN ([FAITS])
LOAD '4 Ventes Industrielles' as [CGE11 NIV1 RESEAU]
AUTOGENERATE 1;

The idea is to reduce my data table on the field  [CGE11 NIV1 RESEAU] where I have the value '4 Ventes Industrielles'.  This turns correctly (without any errors) but instead of only seeing the data for the lines when  [CGE11 NIV1 RESEAU] = '4 Ventes Industrielles' , now all my original data has the value '4 Ventes Industrielles'.  

What is going on here ?  

To be clear, to correctly reduce all my data, would I then need to run through all other table to do this type of inner join on a field to keep only the data that pertains to my wanted set ?  (Ie, items, clients, ...) 

TEST 2 As Test1 didn't work the way I expected, I tried a second idea: 

Script line 1 : Binary 'address of APP1' ;  

Script line later on: 

FAITS_VI:
NoConcatenate
Load *
Resident FAITS
Where left([CGE11 NIV1 RESEAU],1)=4 ;

Drop TAble FAITS;

This ends in an error : Field 'CGE11 NIV1 RESEAU' not found.  

I would greatly apreciate any help!  I really don't like the idea of duplicating the entire script and just adding where functions.

Regards,

[rwunderlich]

The field not found error in TEST2 makes me wonder about the field name. Is [CGE11 NIV1 RESEAU] really a field in FAITS? Or is it in some dimension table? If it's not a field in FAITS then you would be adding the field to every row, no reduction at all.

-Rob

[rwunderlich]

"To be clear, to correctly reduce all my data, would I then need to run through all other table to do this type of inner join on a field to keep only the data that pertains to my wanted set ?  (Ie, items, clients, ...) "

After you reduce the first table, then yes, you would need to Inner Join or Keep on the key field. I don't recommend using Join because you can generate extra rows if the source table has more than one row per value. For example, if you start  your reduction from the Fact table. Using Keep you can avoid this issue or even needing to identify the key field(s). Like this:

temp:
Right Keep (orders)
LOAD * Resident customers;
Drop Table temp;

If  you're worried about reload memory you can tune this slightly by LOADing just the key field instead of "*".

Here's an example to reduce this model.

I've used Subroutines to reduce the duplicate code.

Sub PrintRowCount(_table, _msg)

  Let _vRowCount = NoOfRows('$(_table)');

  TRACE $(_msg) $(_table):$(_vRowCount);

  Set _vRowCount=;

End sub

Sub ReduceTable (_target, _source)

  Call PrintRowCount('$(_target)', 'Before Reduce')

  _Temp:

  Right Keep([$(_target)])

  Load * Resident [$(_source)];

  Drop Table _Temp;

  Call PrintRowCount('$(_target)', 'After Reduce')

End Sub

// Reduce to just the Customer named 'X-Site'

Call PrintRowCount('customers', 'Before Inner Join')

Inner Join (customers)

Load 'X-Site' as Customer AutoGenerate 1;

Call PrintRowCount('customers', 'After Inner Join')

// Reduce the remaining tables, using the relationships.

// First table is the table we want to reduce, 

//  second is the linked table we want to reduce from

Call ReduceTable('Regions', 'customers')

Call ReduceTable('orders', 'customers')

Call ReduceTable('products', 'orders')

Call ReduceTable('stock', 'products')

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[rwunderlich]

I may be getting a little too clever here, but I've updated the SubRoutine to determine the key field(s) for the table pair and am only using those fields in the Keep Load.

Sub PrintRowCount(_table, _msg)
  Let _vRowCount = NoOfRows('$(_table)');
  TRACE $(_msg) $(_table):$(_vRowCount);
  Set _vRowCount=;
End sub

Sub ReduceTable (_target, _source)
  Call KeysForTables('$(_target)', '$(_source)', vKeys)
  Trace Keys: $(vKeys);
  Call PrintRowCount('$(_target)', 'Before Reduce')
  _Temp:
  Right Keep([$(_target)])
  Load $(vKeys) Resident [$(_source)];
  Drop Table _Temp;
  Call PrintRowCount('$(_target)', 'After Reduce')
End Sub

Sub KeysForTables(_table1, _table2, _vReturn)
  _TableFields:
  LOAD
    FieldName(RecNo(), '$(_table1)') as FieldName
  AutoGenerate NoOfFields('$(_table1)');
  Inner Join (_TableFields)
  LOAD
    FieldName(RecNo(), '$(_table2)') as FieldName
  AutoGenerate NoOfFields('$(_table2)');
  _Keys:
 LOAD '[' & Concat(FieldName, '],[') & ']' as Keys
  Resident _TableFields;
  Let _vReturn = Peek('Keys', -1, '_Keys');
  Drop Table _Keys, _TableFields;
End Sub

-Rob

[diagonjope]

Have you tried using Section Access to limit the data rows / fields that
users can see in you current app? Do you really need to make a copy of the
reduced data for other reasons?

If you can't use Section Access, you could do a resident load on each
table needed, while selecting only the records that you need. Remember to
drop each one of the original tables that you reload this way.

++José