reduced datamodel from binary ?

[#1]

Hi Qlikview Community,

how can i load a reduced Field into a qvw app from binary datemodel ?

This is my whole datemodel (first line in qvw) Binary ;

and i want to load only the records of the field [Customer Ordertyp] = 'billings'

How can i do that ?

Thank you very much

Best Regards,

[deepakk]

hi,

Once the binary reload is completed. check for the table name which you want to filter.

noconcatenate

load resident a where [customer ordertype] = 'billing'

[johnw]

A binary load loads everything.

You can reduce manually after the fact by selecting 'billings' and then File -> Reduce Data -> Keep Possible Values.

If you want to reduce it during the load, it's simple if you have QlikView Publisher. The Reduce tab lets you specify a value for a field. Then, "All data not included in the selection will be removed."

If you want to do it during the load, and don't have Publisher, I'm only thinking of what could be a rather complicated approach if you have a lot of related tables in your data model. Specifically, you'd use a sequence of inner joins to reduce the data. Let's say the data model you are binary loading looks like this:

T1:
F1, F2, F3

T2:
F2, F4

And you want to keep only where F4 = 'A'. You'd do the following to reduce the data model:

INNER JOIN (T2)
LOAD 'A' as F4
AUTOGENERATE 1
;
INNER JOIN (T1)
LOAD DISTINCT F2
RESIDENT T2
;

The idea here is to follow all the connections in your data model to wipe out all of the related data from all of the related tables.

There might be an easier way. I don't have much experience with data reduction.

[#4]

hi Deepak,

According your example I have the following statement in the file qvw file:

<SCRIPT>

Binary ;

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

noconcatenate load resident [SALESFORCE] where [SAL_WERBETRAEGER_Name] = 'alpin.de'

</SCRIPT>

Now if I'm running the script, i get this Script Error Message from QV:

Syntax Fehler: FROM fehlt/falsch:
noconcatenate load resident [SALESFORCE] where [SAL_WERBETRAEGER_Name] = 'alpin.de'

Regards,

[johnw]

I believe Deepak's suggestion would translate to this in your actual script:

Binary ;

SALESFORCE2:
noconcatenate load * resident [SALESFORCE] where [SAL_WERBETRAEGER_Name] = 'alpin.de';
DROP TABLE [SALESFORCE];
RENAME TABLE [SALESFORCE2] TO [SALESFORCE];

I think the inner join I suggested would be more efficient, but that should indeed remove all rows but 'alpin.de' from the SALESFORCE table. However, it leaves other tables unchanged, so there may be a lot of data in other tables that no longer has matching data in the SALESFORCE table. That's why I was suggesting a sequence of inner joins rather than just one, one inner join for each table connected in any way to the SALESFORCE table. I'd have to see your full data model to give you exact script, but hopefully you can figure it out.

[#6]

Hi John,

my file ist now uploaded. thank you very much !

[#7]

Hi John,

First of all thank you for your valuable tips.

There are indeed a lot of tables. Unfortunately, I do not know how it with the 2nd and 3 Table looks like. If you give me a continuing example to show the principe how i reduce my records, could be grateful.

The goal is to keep only the data that includes the [SAL_Werbetraeger_Name] = 'alpin.de'.

Thank you very much !

regards,

[johnw]

OK, not too surprising to see a big data model like that. Shouldn't be too hard to work out. I apologize for any misspellings, so you may not be able to cut and paste directly, but you'll probably get the idea.

To explain, I'm just fixing all of your tables in a specific order. First, I fix the SALESFORCE table. Then I fix each table connected to that table. Then for each of those tables, I fix each table connected to them. The inner joins MUST be done in that order, first fixing one table, then fixing the tables connected to it. If you just pick a table at random, the keys you're joining on may not have been fully reduced. Let's see, if you look at tree traversal on wikipedia:

http://en.wikipedia.org/wiki/Tree_traversal

I'd use a preorder breadth-first traversal sequence. However, you could also use a preorder depth-first traversal sequence as well. For that matter, you're a human, so you could mix it up however makes sense to you as long as you start at SALESFORCE and never go to a table except by a link in your data model.

INNER JOIN (SALESFORCE)
LOAD 'alpin.de' as [SAL_Werbetraeger_Name]
AUTOGENERATE 1;

INNER JOIN (BLACKLIST)
LOAD DISTINCT %SAL_PK(SAL_BLACKLIST/ACCOUNT)_ACCOUNTID
RESIDENT SALESFORCE;

INNER JOIN (PLANDATEN)
LOAD DISTINCT %SAL_PK(SAL_WERBETRAEGER/PLANDATEN)_WERBETRAEGERID
RESIDENT SALESFORCE;

INNER JOIN (ACCOUNT_ZIELKUNDENGRUPPE_SUBFIELDS)
LOAD DISTINCT %SAL_PK(SAL_ACCOUNT_ZIELKUNDENGRUPPE_SUBFIELDS)_ACCOUNTID
RESIDENT SALESFORCE;

// and so on for every table connected to SALESFORCE
// one of those tables is ACCOUNT_WERBETRAEGER
// and it has another table, PLANDATENKUNDEN, attached to it
// so we do the same thing with it

INNER JOIN (PLANDATENKUNDEN)
LOAD DISTINCT %SAL_PK(SAL_PLANDATENKUNDEN/ACCOUNT_WERBETRAEGER)_ACCOUNTID
RESIDENT ACCOUNT_WERBETRAEGER;

// and so on until we've done an inner join by key on every table.

And again, I'd hope there is an easier way.

[pover · Accepted solution]

It might be a little easier to use a for-loop like the following:

INNER JOIN (SALESFORCE)
LOAD 'alpin.de' as [SAL_Werbetraeger_Name]
AUTOGENERATE 1;

for each vTable_Field in 'BLACKLIST,%SAL_PK(SAL_BLACKLIST/ACCOUNT)_ACCOUNTID',
'PLANDATEN,%SAL_PK(SAL_WERBETRAEGER/PLANDATEN)_WERBETRAEGERID',
'ACCOUNT_ZIELKUNDENGRUPPE_SUBFIELDS,%SAL_PK(SAL_ACCOUNT_ZIELKUNDENGRUPPE_SUBFIELDS)_ACCOUNTID'

let vTable = subfield(vTable_Field,',',1);
let vField = subfield(vTable_Field,',',2);

INNER JOIN ($(vTable))
LOAD DISTINCT $(vField)
RESIDENT SALESFORCE;

next vTable_Field

Regards.

[johnw]

Good idea! But let's take it a step further so that we only need one loop:

FOR EACH vLink in 'SALESFORCE,BLACKLIST,%SAL_PK(SAL_BLACKLIST/ACCOUNT)_ACCOUNTID',etc. listing every link you're following here in order

LET vFrom = subfield(vLink,',',1);
LET vTo = subfield(vLink,',',2);
LET vKey = subfield(vLink,',',3);

INNER JOIN ($(vTo))
LOAD DISTINCT $(vKey)
RESIDENT $(vFrom);

NEXT vLink

Or maybe set yourself up an inline table and step through it row by row as another option. Or set up an inline table and do a concatenate on it to build the list, just so that it's easier to maintain and see the list.