topic Re: Script Qlikview and T-SQL in QlikView

Script Qlikview and T-SQL

Fri, 02 Dec 2011 11:15:46 GMT

Hello,

I have a SQL statement :

SELECT

CONVERT(DATETIME, LEFT(DATE_CREATION, 4) + '-' + LEFT(RIGHT(DATE_CREATION, 4), 2) + '-' + RIGHT(DATE_CREATION, 2) , 120) AS DATE

FROM dbo.CPT_EXERCICE

My question is, how can I convert this SQL to QlikView script?

Hope that you can help me solve my problem.. Thanks in advance!

Regards

Script Qlikview and T-SQL

jagan — Fri, 02 Dec 2011 12:10:07 GMT

Hi,

You can load the data into Qlikview using the below

TableName:

SQL SELECT

CONVERT(DATETIME, LEFT(DATE_CREATION, 4) + '-' + LEFT(RIGHT(DATE_CREATION, 4), 2) + '-' + RIGHT(DATE_CREATION, 2) , 120) AS DATE

FROM dbo.CPT_EXERCICE;

Regards,

Jagan.

Re: Script Qlikview and T-SQL

Fri, 02 Dec 2011 12:58:34 GMT

Thanks very much for your help

Do you think in two other fields to filter by month (january, feb...) and an another by year (2010, 2011 etc...) ?

regards,

twist

Re: Script Qlikview and T-SQL

jagan — Fri, 02 Dec 2011 13:13:21 GMT

Hi,

Use the following script to get month and year filters

TableName:

LOAD

DATE ,

Month(DATE) AS Month,

Year(DATE) AS Year;

SQL SELECT

CONVERT(DATETIME, LEFT(DATE_CREATION, 4) + '-' + LEFT(RIGHT(DATE_CREATION, 4), 2) + '-' + RIGHT(DATE_CREATION, 2) , 120) AS DATE

FROM dbo.CPT_EXERCICE;

Regards,

Jagan.

Script Qlikview and T-SQL

Fri, 02 Dec 2011 13:23:34 GMT

Great it works very well

but now the other fields have disappeared (EC_CP_CLEUNIK, EC_ENTITE, EC_EX_CLEUNIK....)

but after reload,

my script below in full

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xxxxx;Initial Catalog=COMPTA;Data Source=xxxxxL............];

LOAD

DATE ,

Month(DATE) AS Month,

Year(DATE) AS Year;

SQL SELECT "EC_CP_CLEUNIK",

"EC_ENTITE",

"EC_EX_CLEUNIK",

"EC_JOURNAL",

"EC_LIBELLE",

"EC_LOT",

"EC_MNT_CREDIT",

"EC_MNT_DEBIT",

"EC_MOUVEMENT",

CONVERT(DATETIME, LEFT(EC_DATE_OPERATION, 4) + '-' + LEFT(RIGHT(EC_DATE_OPERATION, 4), 2) + '-' + RIGHT(EC_DATE_OPERATION, 2) , 120) AS DATE

FROM COMPTA.dbo."CPT_ECRITURE";

Script Qlikview and T-SQL

jagan — Fri, 02 Dec 2011 13:30:39 GMT

Hi,

Add the remaining fields in the load and select statement.

TableName:

LOAD

"EC_CP_CLEUNIK",

"EC_ENTITE",

"EC_EX_CLEUNIK",

"EC_JOURNAL",

"EC_LIBELLE",

"EC_LOT",

"EC_MNT_CREDIT",

"EC_MNT_DEBIT",

"EC_MOUVEMENT",

DATE ,

Month(DATE) AS Month,

Year(DATE) AS Year;

SQL SELECT

"EC_CP_CLEUNIK",

"EC_ENTITE",

"EC_EX_CLEUNIK",

"EC_JOURNAL",

"EC_LIBELLE",

"EC_LOT",

"EC_MNT_CREDIT",

"EC_MNT_DEBIT",

"EC_MOUVEMENT",

CONVERT(DATETIME, LEFT(DATE_CREATION, 4) + '-' + LEFT(RIGHT(DATE_CREATION, 4), 2) + '-' + RIGHT(DATE_CREATION, 2) , 120) AS DATE

FROM dbo.CPT_EXERCICE;

Regards,

Jagan.

Script Qlikview and T-SQL

Fri, 02 Dec 2011 13:37:29 GMT

really thank you for your help

it remains for me to figure out how to filter the fields if I no longer wish to reveal the previous year (ex : i have the years 2003 to 2011, but i would like to see that the year 2008 to date)

Regards,

Twist

Script Qlikview and T-SQL

jagan — Fri, 02 Dec 2011 13:45:59 GMT

Try this,

TableName:

LOAD

"EC_CP_CLEUNIK",

"EC_ENTITE",

"EC_EX_CLEUNIK",

"EC_JOURNAL",

"EC_LIBELLE",

"EC_LOT",

"EC_MNT_CREDIT",

"EC_MNT_DEBIT",

"EC_MOUVEMENT",

DATE ,

Month(DATE) AS Month,

Year(DATE) AS Year

WHERE Year(DATE) >= 2008;

SQL SELECT

"EC_CP_CLEUNIK",

"EC_ENTITE",

"EC_EX_CLEUNIK",

"EC_JOURNAL",

"EC_LIBELLE",

"EC_LOT",

"EC_MNT_CREDIT",

"EC_MNT_DEBIT",

"EC_MOUVEMENT",

CONVERT(DATETIME, LEFT(DATE_CREATION, 4) + '-' + LEFT(RIGHT(DATE_CREATION, 4), 2) + '-' + RIGHT(DATE_CREATION, 2) , 120) AS DATE

FROM dbo.CPT_EXERCICE;

Regards,

Jagan.

Script Qlikview and T-SQL

Fri, 02 Dec 2011 13:57:18 GMT

thanks you very much for your help