Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Joining SQL Queries in Load and manipulate in memory before QVD

Here is the scenario. I have several sql pulls that I need to make into memory, joining them together and then doing calculations based on those fields before writing out a qvd file. For example.

Let's say I need to.

1) select visitid as [Visit ID],

name as [Name],

location as [Location],

cost1 * 3.23 as [Cost 1],

cost2 * 5.33 as [Cost 2]

from db1;

2) select visitnum as [Visit ID],

cost3 as [Cost 3]

cost4 / 5.64 as [Cost 4]

from db2;

Then I want to do calculations on these fields such as

[Cost 1] / [Cost 3]*[Cost4] as [Cost Total 1]

This is a very simplified example but it should do the trick...

What I am wanting to do is load the first sql into memory, then join the second sql in memory and then do the calculations on the columns (using the column names instead of field names and finally produce a single qvd file. I think you have to use resident tables and joins, but I am not clear on the syntax to get this done... Any help with detailed scripting syntax would be a lifesaver. Thanks ahead of time.

Tags (1)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Joining SQL Queries in Load and manipulate in memory before QVD

Brian,

Something like this:

table_tmp:
select visitid as [Visit ID],
name as [Name],
location as [Location],
cost1 * 3.23 as [Cost 1],
cost2 * 5.33 as [Cost 2]
from db1;

INNER JOIN (table_tmp)

SQL select visitnum as [Visit ID],
cost3 as [Cost 3]
cost4 / 5.64 as [Cost 4]
from db2;

table:


LOAD
[Visit ID],
Name,
Location,
[Cost 1] / [Cost 3]*[Cost4] as [Cost Total 1]
RESIDENT table_tmp;
DROP TABLE table_tmp;

STORE table into table.qvd (qvd)

3 Replies

Re: Joining SQL Queries in Load and manipulate in memory before QVD

Something like this:

Table1:

select visitid as [Visit ID],

name as [Name],

location as [Location],

cost1 * 3.23 as [Cost 1],

cost2 * 5.33 as [Cost 2]

from db1;

JOIN(Table1)

select visitnum as [Visit ID],

cost3 as [Cost 3]

cost4 / 5.64 as [Cost 4]

from db2;

Result:

Load *, [Cost 1] / [Cost 3]*[Cost4] as [Cost Total 1]

Resident Table1;

drop table Table1;

STORE Result into result.qvd;


talk is cheap, supply exceeds demand
mov
Esteemed Contributor III

Re: Joining SQL Queries in Load and manipulate in memory before QVD

Brian,

Something like this:

table_tmp:
select visitid as [Visit ID],
name as [Name],
location as [Location],
cost1 * 3.23 as [Cost 1],
cost2 * 5.33 as [Cost 2]
from db1;

INNER JOIN (table_tmp)

SQL select visitnum as [Visit ID],
cost3 as [Cost 3]
cost4 / 5.64 as [Cost 4]
from db2;

table:


LOAD
[Visit ID],
Name,
Location,
[Cost 1] / [Cost 3]*[Cost4] as [Cost Total 1]
RESIDENT table_tmp;
DROP TABLE table_tmp;

STORE table into table.qvd (qvd)

Not applicable

Re: Joining SQL Queries in Load and manipulate in memory before QVD

OK, syntax should be something like

Data:

LOAD visitid AS [Visit ID],

name as [Name],

location as [Location],

cost1 * 3.23 as [Cost 1],

cost2 * 5.33 as [Cost 2]

from db1;

left join (Data)

LOAD visitnum as [Visit ID],

cost3 as [Cost 3],

cost4/5.64 as [Cost 4]

from db2;

FinalTable:

NoConcatenate

LOAD *,

[Cost 1] / [Cost 3] * Cost 4] as [Cost Total 1]

RESIDENT Data;

DROP TABLE Data;

STORE FinalTable INTO FinalTable.QVD;

Community Browser