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

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

5 Replies

Re: I am new in qlikview please help me

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

Not applicable

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

Not applicable

Re: I am new in qlikview please help me

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.

Not applicable

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 Smiley Sadthis is the second table name in script)

LOAD

fields........

MVP
MVP

Re: I am new in qlikview please help me

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.

Community Browser