Script Qlikview and T-SQL

[#1]

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

[jagan]

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.

[#3]

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

[jagan]

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.

[#5]

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";

[jagan]

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.

[#7]

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

[jagan · Accepted solution]

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.

[#9]

thanks you very much for your help