topic Question about load data from two tables with SQL. in QlikView

Question about load data from two tables with SQL.

Anonymous — Tue, 10 Feb 2015 15:20:23 GMT

Hello,

Escuse me, I have a problem. this is not many time I start with QlikView and I have many question.

I need to load rows data of quantity and amount per article but I have this data in two tables with different name and different fields names.

I load with SQL anfd I user the table item.

So:

ODBC CONNECT TO DATA01;

// First table with rows with quantity and Amount
Table1:
SQL SELECT
    Table1_ItemCode AS Table3_ItemCode,
    Table1_Quantity,
    Table1_Amount
FROM Table1;

// Second table with rows with quantity and Amount
Table2:
SQL SELECT
    Table2_ItemCode AS Table3_ItemCode1,
    Table2_Quantity,
    Table2_Amount
FROM Table2;

// Item Table

Table3:
SQL SELECT
    Table3_ItemCode,
    Table3_ItemCode AS Table3_ItemCode1,
    Table3_ItemDescription,

I must do a temporary table? How?

I dont want use Synth Key also (I have use a alias in Table3 for Table 2 but I dont know if this is the right way).

Thank you very mych!!!

Best regards.

Re: Question about load data from two tables with SQL.

rubenmarin — Tue, 10 Feb 2015 15:29:57 GMT

Hi Giuseppe, you can concatenate table 1 and table 2, table 3 can be a left join with the result table if ItemCode is unique, or leave it in a separated table if there are duplicated ItemCode or if is better for the model to keep in another table:

FactTable:

SQL SELECT

Table1_ItemCode AS ItemCode,

Table1_Quantity as Quantity,

Table1_Amount as Amount

FROM Table1;

Concatenate (FactTable)

LOAD *;

SQL SELECT

Table2_ItemCode AS ItemCode,

Table2_Quantity as Quantity,

Table2_Amount as Amount

FROM Table2;

Left Join(FactTable) <-- Change this line by "Items:" if you want it in separated table

LOAD *;

SQL SELECT

Table3_ItemCode as ItemCode,

Table3_ItemDescription as Description

FROM ItemTable;

Re: Question about load data from two tables with SQL.

maxgro — Tue, 10 Feb 2015 15:31:24 GMT

do you want to add articles in table2 to articles in table1 (like a union all in SQL)?

if yes use a concatenate

Re: Question about load data from two tables with SQL.

Anonymous — Tue, 10 Feb 2015 16:59:26 GMT

Thank you Ruben and Massimo!

Now is all okay!

I need to lear better QLV Scripting but now is very clear!

Best regards

Re: Question about load data from two tables with SQL.

maxgro — Tue, 10 Feb 2015 17:03:56 GMT

for some help about loading data in QlikView you can read this

LOAD data into QlikView

Re: Question about load data from two tables with SQL.

Anonymous — Tue, 10 Feb 2015 17:06:10 GMT

Thank you for good suggestion Massimo!

Re: Question about load data from two tables with SQL.

Anonymous — Tue, 10 Feb 2015 19:27:20 GMT

Can I ask another think?

I have both in Table1 and in Table2 a date now (DATAREG).

I created a master calendar but it dont work proprely if I call the master calendar creation after load the table.

Maybe I must order for date filed the FactsTable after I created this?

I tryed but I dont know where I can write the ORDER BY statement, if I write this at the end of the load og TableItem ad before the master calendar I have an error.

Thank you again!

Re: Question about load data from two tables with SQL.

maxgro — Tue, 10 Feb 2015 20:15:03 GMT

You should have (from Ruben answer) a FactTable with a DATEREG field and the same field (DATEREG) in MasterCalendar to link with FactTable.

There is no need to order the table, you usually order the charts in the sort tab.

If I missing something, please post the scriptor, better, a small example with data

Re: Question about load data from two tables with SQL.

Anonymous — Wed, 11 Feb 2015 09:29:57 GMT

Thank you for your answer Massimo.

Yes, I do what you and Ruben write but dont work.

So, the date is DATARG and this is the script:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';

// Data from System i (AS/400)

ODBC CONNECT TO DATA01;

//-------- Start Multiple Select Statements ------

Facts:
SQL SELECT
    Table1_ItemCode AS Table3_ItemCode,
    DATE(timestamp_format(digits(Table1_Date),'YYYYMMDD' )) AS DATARG,
    Table1_Quantity AS Quantity,
    Table1_Turnover AS Turnover
FROM Table1;

Concatenate(Facts)
LOAD *;
SQL SELECT
    Table2_ItemCode AS Table3_ItemCode,
    DATE(timestamp_format(digits(Table2_Date),'YYYYMMDD' )) AS DATARG,
    Table2_Quantity AS Quantity,
    Table2_Turnover AS Turnover
FROM Table2

Left Join(Facts)
LOAD *;
SQL SELECT
    Table3_ItemCode,
    Table3_ItemDescription,
    FROM Table3

//-------- End Multiple Select Statements ------

$(Include=..\inclusioni\mastercalendar.txt);

The mastercalendera. txt that I include is this:

let varMinDate = num(peek('DATARG',0,'Facts'));
let varMaxDate = num(peek('DATARG',-1,'Facts'));

TempCalendar:
Load

date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) +1;

MasterCalendar:
LOAD
date(TempDate) AS DATARG,
week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
inyeartodate (TempDate,Date(Today()), 0) *-1 as CYTDFlag,
inyeartodate (TempDate,Date(Today()), -1) *-1 as LYTDFlag,
inyear (TempDate,Date(Today()), 0) *-1 as CYFlag,
inyear (TempDate,Date(Today()), -1) *-1 as LYFlag,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
'T' & ceil(month(TempDate)/3) as Quarter,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
weekday(TempDate) AS WeekDay

RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

I have two problem:

- I dont have Year 2015 (I have rows with year 2015 in Table1 but not in Table2 and I have in Table1 date from 08/09/1992 but I see Year only from 1994, in Mastercalender Table I see date from 08/09/1992 to 10/02/2015, so I dont understand what Qlikview do and where QlikView find data with wrong month, I controlled the data both of Table1 and Table2 in AS/400 and these is okay);

- if I write a bar chart with Month in dimension I have the month from Jan to Dic and some data without Month and Quarter.

I attach an example screenshot:

Thank you again.

Re: Question about load data from two tables with SQL.

rubenmarin — Wed, 11 Feb 2015 10:59:11 GMT

Hi Giuseppe,

Try to set varMinDate and varMaxDate this way:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG;

let varMinDate = num(peek('DATARG',0,'TempDates'));

let varMaxDate = num(peek('DATARG',-1,'TempDates'));

DROP Table TempDates;

Re: Question about load data from two tables with SQL.

Anonymous — Wed, 11 Feb 2015 11:19:28 GMT

Hi Ruben,

so my mastercalendar now is:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG;

let varMinDate = num(peek('DATARG',0,'TempDates'));
let varMaxDate = num(peek('DATARG',-1,'TempDates'));

DROP Table TempDates;

TempCalendar:
Load

date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) +1;

RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

but if I load the data from Qlikview I have this error:

Script Error

TempCalendar:
Load

date(42046 + rowno() -1) as TempDate
autogenerate 37148 - 42046 +1

Please can you help me, maybe I dont understand what I must to do.

Thank you very much!

Re: Question about load data from two tables with SQL.

rubenmarin — Wed, 11 Feb 2015 11:32:27 GMT

Hi, not sure why, but is getting switched MinDate and MaxDate, lets give another name to the TempDates field:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

If continues switching varMinDate and varMaxDate let's try to sort TempDates descending, maybe date format sorts different but it will be weird:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp desc;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

Re: Question about load data from two tables with SQL.

Anonymous — Wed, 11 Feb 2015 11:39:53 GMT

This dont work, if I use:

TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

TempCalendar:
Load

Date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) +1;

RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

I have the error:

Field not found
TempDates:

NoConcatenate LOAD Distinct DATARG Resident Facts Order By DATARG_tmp

I must to use a alias?

How?

Thank you!

Re: Question about load data from two tables with SQL.

rubenmarin — Wed, 11 Feb 2015 11:43:59 GMT

Sorry, the Order By should be:

NoConcatenate LOAD Distinct DATARG as DATARG_tmp Resident Facts Order By DATARG;

Re: Question about load data from two tables with SQL.

Anonymous — Wed, 11 Feb 2015 11:55:17 GMT

Not a problem Ruben, I thank you very much!

Unfortunatelly this dont work with:

TempDates:

NoConcatenate LOAD Distinct DATARG as DATARG_tmp Resident Facts Order By DATARG;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

TempCalendar:
Load

Date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) +1;

RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

I have no error but I have the bar chart with this data:

So now I have many data without month.

I dont know what can I do!

Re: Question about load data from two tables with SQL.

rubenmarin — Wed, 11 Feb 2015 12:04:28 GMT

I don't know why but is taking xx/xx/2001 as MaxDate, Facts table is concatenated before creating calendar, isn't it?

Can you upload a sample or at least the reload log?

I tested with sample data and works:

Facts:

LOAD Date(DATARG) as DATARG;

LOAD * Inline [

DATARG

01/01/1992

11/02/2015

];

TempDates:

....

This returns me all dates from 01/01/1992 to 11/02/2015 with all his months, weeks, etc...

Re: Question about load data from two tables with SQL.

Anonymous — Wed, 11 Feb 2015 12:17:16 GMT

So, I repeat the Script:

// Data from System i (AS/400)

ODBC CONNECT TO DATA01;

//-------- Start Multiple Select Statements ------

//-------- End Multiple Select Statements ------

$(Include=..\inclusioni\mastercalendar.txt);

The mastercalendar:

TempDates:

NoConcatenate LOAD Distinct DATARG as DATARG_tmp Resident Facts Order By DATARG;

let varMinDate = num(peek('DATARG_tmp',0,'TempDates'));
let varMaxDate = num(peek('DATARG_tmp',-1,'TempDates'));

DROP Table TempDates;

TempCalendar:
Load

Date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) +1;

RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

I work wit a desktop version and I dont know where log is stored, sorry.

Re: Question about load data from two tables with SQL.

rubenmarin — Wed, 11 Feb 2015 12:53:36 GMT

Hi, I can't take more info from the script, I needed the document to check what kind of dates has stored in DATARG.

You can comment quantity and turnover fields if you don't want to upload that data, I only need the dates from facts and calendar tables..

BTW, to create the log you need to check, in 'Configurations' menu-->Document Properties--> General Tab, check 'Generate log file'

It's created in the same folder than the qvw.

Re: Question about load data from two tables with SQL.

Anonymous — Wed, 11 Feb 2015 13:28:11 GMT

Hi, thank you.

I attach files without sensitive data.

Re: Question about load data from two tables with SQL.

rubenmarin — Wed, 11 Feb 2015 14:17:57 GMT

I'm still lost, DATARG has dates till 11/02/2015 and it really is date format, why is taking 25/05/2005 as max date?

Another way to retrieve varMaxDate and varMinDate:

TempDates:

LOAD Min(DATARG) as MinDATARG,

Max(DATARG) as MaxDATARG

Resident Facts;

let varMinDate = Num(FieldValue('MinDATARG', 1));
let varMaxDate = Num(FieldValue('MaxDATARG', 1));

DROP Table TempDates;