21 Replies Latest reply: Feb 11, 2015 9:30 AM by Ruben Marin RSS

    Question about load data from two tables with SQL.

    Giuseppe Gallina

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

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

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

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

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

                        Thank you for good suggestion Massimo!

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

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

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

                                  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:Chart01.jpg

                                   

                                  Thank you again.

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

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

                                          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!

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

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

                                                  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!

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

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

                                                          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!

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

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

                                                                  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.