5 Replies Latest reply: Nov 4, 2014 4:09 AM by jagan mohan rao appala Branched to a new discussion. RSS

    I am new in qlikview please help me

      --one table

       

      select PersonID,PositionID,MONTH(StartDate),Year(StartDate) from NND_MSt_person

       

      --second table

      select ME,TPosition,Year,Month from NND_Sales_fact

       

      --i want to implement this sql query in QlikView Scripting How can i achieve THIS

       

       

      select PersonID,PositionID,MONTH(StartDate),Year(StartDate) ,s.netsales

      from NND_MSt_person p

      join NND_Sales_fact s on p.PersonID=s.ME and p.positionid =s.TPosition

      and MONTH(p.StartDate)=s.Month and Year(p.StartDate)=Year

        • Re: I am new in qlikview please help me
          Peter Cammaert

          In your script, create a CONNECT statement. Use the wizard if you don't know the syntax (Connect button)

           

          After this connect statement, add your SQL statements as they are, but do prefix them with a SQL keyword and terminate them with a semicolon , e.g. like

           

          CONNECT ...;

           

          SQL SELECT PersonID,PositionID,MONTH(StartDate),Year(StartDate) ,s.netsales

          FROM NND_MSt_person p

          JOIN NND_Sales_fact s ON p.PersonID=s.ME AND p.positionid =s.TPosition

          AND MONTH(p.StartDate)=s.Month AND Year(p.StartDate)=Year;

           

          For good measure, add a table name before each SQL SELECT statement.

          If it works for your database engine, it'll work for QlikView.

           

          Best,

           

          Peter

            • Re: I am new in qlikview please help me

              my question is how can i implement this in qlikview script

              say for person table i am written one script and saved in person qvd

              anather i saved in sales qvd

               

              now i want to get both qvd with these join condition,How can i get this

              please help me

                • Re: I am new in qlikview please help me
                  jagan mohan rao appala

                  Hi,

                   

                  Try like this

                  NND_MSt_person:

                  select PersonID,

                       PositionID,

                       MONTH(StartDate),

                       Year(StartDate)

                  from NND_MSt_person;

                   

                  STORE NND_MSt_person INTO NND_MSt_person.qvd;


                  DROP TABLE NND_MSt_person ;


                  NND_Sales_fact:

                  select ME AS PersonID,

                  TPosition AS PositionID,

                  Year,

                  Month,

                  netsales

                  from NND_Sales_fact;


                  STORE NND_Sales_factINTO NND_Sales_fact.qvd;

                  DROP TABLE NND_Sales_fact;


                  Now to load QVDs into qlikview file

                   

                  Try this script

                   

                  Data:

                  LOAD

                  *

                  FROM NND_MSt_person.qvd (qvd);

                   

                  LEFT JOIN (Data)

                  LOAD

                  *

                  FROM NND_Sales_fact.qvd (qvd);

                   

                  Hope this helps you.

                   

                  Regards,

                  Jagan.

              • Re: I am new in qlikview please help me
                Felim Shanaghy

                Can I suggest you try the free training on the Qlik.com website it is not very long but will guide you through your query step by step.

                • Re: I am new in qlikview please help me

                  What you have to do first is to connect your database(MySql database) with Qlikview using MySql connecter and use ODBC connecter to connect MySql database with your Qlikview.

                  Connecting QlikView to MySql database:

                  open QlikView - click on Edit Script and then choose OLE DB on databases then click on connect. After choose Microsoft OLE DB provider for ODBC Drivers and then next. in use data source name, chooese youre mySql database name then click test connection. if it says ted connection succeded then your are good to go. then click Ok

                  your database will be loaded into QlikView then you can manipulate your data as you wanted.

                  I hope this help.

                   

                  If your database is already connected to  qlikview you have to select your two tables and join them after in the script

                  if you are writing them in the script you can use loin table condition like this:

                   

                  person: (this is the first table name in script)

                  LOAD

                  name,

                  ID_person,

                  ........

                  from.....

                   

                  join(oerson)     this means joining the sales table into person

                  sales :(this is the second table name in script)

                  LOAD

                  fields........