Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
amirjabbar1900
Not applicable

how to write script in data modeling for sub query and tree

Hello Everyone,

                      i have a question , i m writing a query which have sub query and tree , i want to write in qlike sence data modeling ,

fallowing are the query script

WHERE MST.VNO=DTL.VNO

AND DTL.ACID=COA.ID

AND ID IN (SELECT ID FROM NGL_COA START WITH ID='20000000' CONNECT BY PRIOR ID=PACID)

4 Replies
jaimeaguilar
Not applicable

Re: how to write script in data modeling for sub query and tree

Hi,

Even though some functions/syntax may seem similar among Qlik and SQL they're quite different in the way the work. So translating a query from SQL to Qlik sometimes could be a bad idea.

Instead, the approach I would recommend is to use the SQL query that you already have and extract it in Qlik Sense. You can execute almost any SQL query in QlikView / Qlik Sense, you just have to follow this structure in the data load editor:

[Qlik Table]:

Load *, Field1 + Field2 as Field3; //ADDITIONALLY YOU CAN ADD NEW FIELDS WITH QLIK SYNTAX

SQL Select Field1, Field2, Field N

from db.user.table

WHERE MST.VNO=DTL.VNO

AND DTL.ACID=COA.ID

AND ID IN (SELECT ID FROM NGL_COA START WITH ID='20000000' CONNECT BY PRIOR ID=PACID)

Everything after keyword "SQL" can be any SQL Query, and the way it works is that Qlik sends the query to the database manager and then just receives the result.

So you can combine SQL queries with Qlik Syntax just like in the above example

hope this helps,

regards

amirjabbar1900
Not applicable

Re: how to write script in data modeling for sub query and tree

hello jaimeaguilar

                 Can u guide me any book or tutorial so can i can see through qlik syntax ,so cant i can convert my sql queries in qlik sense syntax ?

jaimeaguilar
Not applicable

Re: how to write script in data modeling for sub query and tree

If you like want to learn more about qlikview I would recommend you the book "QlikView for developers" by Harry Harmsen and Miguel García. By reading and following the exercises you'll learn in general terms what (and some best practices) can be done with qlikview in a real project. The rest is just experience and practice,

hope this helps,

regards

amirjabbar1900
Not applicable

Re: how to write script in data modeling for sub query and tree

thanks alot jaime , would this book also applicable for qliksence ?