topic Re: Load and Store Statements in QlikView

Load and Store Statements

Thu, 13 Dec 2012 21:55:46 GMT

Hello Community,

Do I have to load the table first before I can place a store statement in the script? Attached is a sample script on a word doc.

Another question I have is when I run the debugger it shows:

2008<<QV DSS 08

2008<<QV DSS 09

2008<<QV DSS 10

Shouldn't the first part with the table names so it reads:

2008<<QV DSS 08

2009<<QV DSS 09

2010<<QV DSS 10

I am open to opinions on structuring my tables I have just been pulling annual reports from Crystal and loading txt files.

Thank you,

Re: Load and Store Statements

Thu, 13 Dec 2012 22:06:19 GMT

Hi,

the reason you see '2008<<' for all tables, is that Qlikview holds identical structure in the same table.

for your first load, you created the table 2008, when you load the second table it has the same names for all fields, automatically it loads the data to the existing table. and the same happenes for 2010.

It depends what you are looking to do with the data:

You can use 'Noconcatenate' before the load statement and have the 3 tables seperatly.

You can add a field 'Year' and load it with the correct data for eacl load:

First load will be

2008 AS Year, etc

Goodluck

Re: Load and Store Statements

Fri, 14 Dec 2012 14:59:58 GMT

Right..it would be dumb to have 4 versions of the same table broken up by year. Is it possible to have 1 table with 4 sources? (DSS 08, 09 etc.) Or is the way I have it set simplest? It feels a little bizarre having 1 big table but those 80 fields are all the fields I need...new to the developer side of things so not sure if there is an advantage to creating more table relationships.

Re: Load and Store Statements

Gysbert_Wassenaar — Fri, 14 Dec 2012 19:48:42 GMT

You can add extra fields for example Year and Source. That way you get all data into one table, but are still able to find out where the data came from. This is the recommended approach in cases as yours.

MyDataTable:

LOAD

2008 as Year,

'QV DSS 08.txt' as Source,

@1 as [CAR DEL FAC],

.... etc

LOAD

2009 as Year,

'QV DSS 09.txt' as Source,

@1 as [CAR DEL FAC],

.... etc

Store MyDataTable into MyDataQVD.qvd (qvd);

Re: Load and Store Statements

farolito20 — Fri, 14 Dec 2012 22:49:35 GMT

How can I compare my database and my qvd, to store only different data?

For example my qvd:

ID, Name, Age

001,Ana,18

And my database

ID,Name,Age

001,Ana,19

I wanna put the new values, but keep old data too. Supose that we don't have a modified date

Re: Load and Store Statements

Sat, 15 Dec 2012 10:56:30 GMT

Hi,

You can do one of two things:

1. For loading both tables but removing duplicates - load both tables with a simple load statement:

table1:

Load ID,

Name,

Age

from table 1

Load ID,

Name,

Age

from table2

and then use the distinct command

noconcatenate

Table2:

load distinct

ID,

Name,

Age

resident Table1;

Drop Table1;

now Table2 will rows of both tables only once for duplicates.

2. for loading ONLY different rows for the second table: you can use the function not exists.

In order to use it, you have to have the first table in memory (resident), and then load the second table.

You can do it like this

table1:

Load ID ,

Name,

Age AS Age1

from table 1.qvd

table2:

Load ID,

Name,

Age

from table2.qvd

where not exists(Age1,Age);//notice that you first write the field in the resident table

now table 2 holds the rows that don't exist in table1. you can write:

load ID,

Name,

Age1 AS Age

resident table1;

drop table1;

and both originals tables will be in table2;

Goodluck!

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 14:26:20 GMT

So, the store will be:

STORE tabla2 into tabla1.qvd (qvd);

Re: Load and Store Statements

Mon, 17 Dec 2012 14:30:13 GMT

If you refer to the first option, then yes.

You can store it under every name you find suitable:

store table2 into XXX.qvd(qvd);

when XXX can be 'table1'

Goodluck!

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 14:33:17 GMT

If I use STORE tabla2 into tabla1.qvd (qvd), I saved duplicates. So I'm using

STORE tabla1 into tabla1.qvd (qvd);

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 14:34:58 GMT

Do you know the way to save in my qvd the time when I added a new data in qvd?

Re: Load and Store Statements

Mon, 17 Dec 2012 14:47:04 GMT

To which option do you refer? 1 ot 2?

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 14:48:41 GMT

First

Re: Load and Store Statements

Mon, 17 Dec 2012 14:51:04 GMT

Add to the table the next field:

load Name,

ID,

Age,

Now() AS Date

from XXX;

and each row will have the date and time of the loading time..

Goodluck!

Re: Load and Store Statements

Mon, 17 Dec 2012 14:54:32 GMT

In the first option table1 should be dropped (as attached). follow the script:

table1:

Load ID,

Name,

Age

from table 1

Load ID,

Name,

Age

from table2

and then use the distinct command

noconcatenate

Table2:

load distinct

ID,

Name,

Age

resident Table1;

Drop Table1;

store table2 into XXX.qvd(qvd); //now Table2 will hold rows of both tables only once for duplicates.

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 14:55:21 GMT

In table 2 LOAD?

Re: Load and Store Statements

Mon, 17 Dec 2012 14:59:58 GMT

In this part:

noconcatenate

Table2:

load distinct

ID,

Name,

Age,

now() as Date

resident Table1;

Drop Table1;

store table2 into XXX.qvd(qvd);

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 15:03:35 GMT

tabla1:

LOAD ID,

Name,

Age

FROM prueba.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD ID,

Name,

Age

FROM tabla1.qvd (qvd);

NoConcatenate

tabla2:

LOAD Distinct

ID,

Name,

Age,

Now() AS Date

Resident tabla1;

STORE tabla2 into bk.qvd (qvd);

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 15:04:28 GMT

all dates were modified

Re: Load and Store Statements

Mon, 17 Dec 2012 15:08:40 GMT

drop table1 before the 'STORE' command.

Modified? to what?

Re: Load and Store Statements

farolito20 — Mon, 17 Dec 2012 15:10:50 GMT

When I put drop1 tabla1 give an syntax error