Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Question about load data from two tables with SQL.

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.

21 Replies
Anonymous
Not applicable
Author

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;

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;

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!

rubenmarin

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;

Anonymous
Not applicable
Author

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;

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 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!

rubenmarin

Sorry, the Order By should be:

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

Anonymous
Not applicable
Author

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;

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 no error but I have the bar chart with this data:

Chart02.jpg

So now I have many data without month.

I dont know what can I do!

rubenmarin

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...

Anonymous
Not applicable
Author

So, I repeat 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 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;

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 work wit a desktop version and I dont know where log is stored, sorry.

rubenmarin

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.

Anonymous
Not applicable
Author

Hi, thank you.

I attach files without sensitive data.

rubenmarin

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;