topic Re: STORE a large QV model in QlikView

STORE a large QV model

zagzebski — Wed, 29 Jan 2014 15:06:48 GMT

I want to store a large QV model with 2 main fact tables, 1 linker table and about 10 dimension tables.

I don't need the majority of the fields (nor some of the tables).

What is the best way to STORE the model while excluding some fields, several tables and a where clause to restrict what gets stored?

Is it possible to STORE a binary load, after droping fields and tables and adding a where clause? I am trying to avoid having to JOIN all the tables manually to get my STORED table, rather than using QlikViews natural key joins.

Steve

Re: STORE a large QV model

Carlos_Reyes — Wed, 29 Jan 2014 15:21:48 GMT

It ain't possible to store a data model per se. You'll have to store each table in a qvd. The only way you could "store a data model" would be to load all the tables in a qvw while using WHERE and EXISTS to apply your restrictions. Then save the qvw and import the "ready" data model using Binary loads.

The only other way would be to join all tables and store a single qvd.

There are no alternative to those methods as far as I know.

Re: STORE a large QV model

Anonymous — Wed, 29 Jan 2014 15:30:08 GMT

Steve,

If I'm understanding you correctly, you want to store tables from a separate QVW into QVDs by using binary load?

What you suggest will work just fine. Perform a binary load, then use "DROP Fields" and "DROP Tables" statements to remove the fields and tables that you don't want. After that, just use "STORE ... into ..." to store the tables.

If you want to restrict the rows by some field, you can do something like this:

Inner Keep(SomeTable)

OtherTable:

LOAD

RestrictingField

Resident

SomeTable

Where

RestrictingField = Criterion

;

DROP Table

OtherTable

;

Hope this helps.

Jeff

Re: STORE a large QV model

Anonymous — Wed, 29 Jan 2014 15:32:20 GMT

Carlos Alberto Reyes Díaz is also right about the Binary load.

You can use the QVW to store the entire model and then use another binary load to import the whole model all at once instead of several QVDs.

Re: STORE a large QV model

rwunderlich — Wed, 29 Jan 2014 15:38:13 GMT

"Is it possible to STORE a binary load, after dropping fields and tables and adding a where clause? "

Yes. Binary load the source model. Use DROP FIELDS and DROP TABLES statements to remove entire fields/tables.

To remove rows (the WHERE clause) us INNER JOIN. For example, if the list of values to be kept is known:

INNER JOIN (Facts) LOAD * INLINE [

Year

2014

2013

2012

]

;

If you need a true where -- for something like less than, use a self INNER JOIN like:

INNER JOIN (Facts) LOAD Year RESIDENT FACTS

WHERE Year >= Year(today())-2;

You can follow that up with some KEEP statements against the Dim tables to make them match the Facts if necessary.

-Rob

Re: STORE a large QV model

zagzebski — Wed, 29 Jan 2014 15:52:25 GMT

Thanks Rob. Sorry new to STORE.

But I still have to Store each table independently right? What I really want is to imitate what I could get from a straight table (which obviously brings in fields across many tables) but since it would be way to large I need to use a STORE command. A straight table would pull fields across different tables - I don't see how that can be done in a store command since it requires a specific table to be stored.

Re: STORE a large QV model

zagzebski — Wed, 29 Jan 2014 15:54:55 GMT

Jeff - my main issue is I want to have one text file in the end that can be loaded into SAS. It looks like what you are suggesting would create separate text files?

Re: STORE a large QV model

Anonymous — Wed, 29 Jan 2014 16:30:38 GMT

Ok, that's a horse of a different color. There's no magic bullet here. You'll need to join them individually in the script. Your other option is to do some messy macro and batch automation that exports a straight table out from a QVW.

To do it in the load script, you can binary load the other QVW, then:

Either use one of the fact tables or create a new table from either of the two
Use {Left | Inner | Right} Join(TableName) to join all the fields that you want to the new table
Use STORE TableName into FileName.txt (txt); to store the table.
DROP the table

So it would look something like this (assuming you create a new table):

Binary ...;

NewTable:

NoConcatenate

LOAD

fields...

Resident

Fact1

Where

criteria

;

Inner Join(NewTable)

LOAD

fields...

Resident

Fact2

Where

criteria

;

Left Join(NewTable)

LOAD

...

STORE

NewTable

into

NewTable.txt (txt)

;

DROP Table

NewTable

;