STORE a large QV model

[zagzebski]

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

[Carlos_Reyes]

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.

[Anonymous]

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

[Anonymous]

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.

[rwunderlich]

"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

[zagzebski]

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.

[zagzebski]

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?

[Anonymous]

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:

1. Either use one of the fact tables or create a new table from either of the two
2. Use {Left | Inner | Right} Join(TableName) to join all the fields that you want to the new table
3. Use STORE TableName into FileName.txt (txt); to store the table.
4. 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

;