topic Creating New Tables using Existing table values (QV script) in QlikView

Creating New Tables using Existing table values (QV script)

Tue, 01 Mar 2011 12:50:56 GMT

Hi,

Am trying to manipulate existing tables in an exisiting QV doc, to create a new table that shares similar fields...

"Purchases" = is the existing table name that I would like to extract data into a new table.

Initial thoughts were that the following script should be able to return "Euros" as a single field in a new table:

RSTable:
Load Euros;
SQL Select *
From Purchases;

following a save, and then a reload, the following error message appears:

ODBC connection failed

SQL Select*

From Purchases

thinking that perhaps I have mis-specified the location of the "Purchases" table (which exists in the current QV file that I am using), tried to specify "Data from files" as "Qlikview File" (relative paths ticked), and created the following syntax:

Binary "allpurchases_rs 110301.qvw";
Load Euros;
SQL Select *
From Purchases;

The above returns the following error message:

"Unknown statement. Binary "allpurchases_rs 110301.qvw"

(the actual file that I am using does contain capital letters, although the wizard script automatically converts the file name to lower case. Have adjusted the script to reflect exact cases, but issue still remains.)

In the past, have completed the "developer 1" e-learing course and was able to access the various datasources, and load/manipulate data as per the excercises. However, trying to replicate this in an already existing QV doc, am expericancing a few issues.

Anyone have any thoughts in what I am doing wrong?

Please advise,

Kind regards,

Rich

SV:Creating New Tables using Existing table values (QV script)

Tue, 01 Mar 2011 13:42:55 GMT

If a table already exist in the load you need to use a resident load to access its data. I.E

Load Euros
RESIDENT Purchases;

A binary statement needs to be placed first in the script.

/Michael

SV:Creating New Tables using Existing table values (QV script)

Tue, 01 Mar 2011 14:41:33 GMT

Hi Michael,

Many thanks!

How should the above syntax be eddited when I want to add additional fields from a different table (ie 2 fields from "Purchases", and another from "Materials"

RSTable:

Load Euros,
Year
RESIDENT Purchases;
Load
Family
RESIDENT Materials;

My script above, currently creates 2 tables (but want just the one table, with 3 fields in the "RSTable".

Any thoughts greatly appreciated,

Kind regards,

Rich

Creating New Tables using Existing table values (QV script)

matt_crowther — Tue, 01 Mar 2011 15:46:34 GMT

Rich,

I've not thought this through in great detail (which is never a good thing) but the first thing that jumps out at me is to use the Join statements:

2 Tables: Customers and Employees.

Lets say you want to create a new table of employees with the relevant Customer name within the same table - assuming there is a 'CustomerID' or similar on both tables then the way I'd go about it is as follows:

NewTable:

Load

Employee,

CustomerID

Resident Employees;

Left Join (NewTable)

Load CustomerID,

CustomerName

Resident Customers;

Qlikview will make the 'association' on the 'CustomerID' field and attach the CustomerName where there's a match onto Employee.

Take a look in the help file under 'Joins'.

Hope that helps,

Matt - Visual Analytics Ltd