topic Re: SQL and LOAD statement in QlikView

SQL and LOAD statement

Thu, 21 Aug 2014 17:59:53 GMT

I have connected into an Access db with 2 two tables using the following script. What I would like to do in a LOAD statement is add a new field which concatenates the First_Name and Last_Name field as 'Full Name'. Where does the LOAD statement go in the script below. I want to create a new table using the LOAD script which will also contain all the fields from the Access db and custom fields i code?

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO [MS Access Database;DBQ=C:\Users\szadroga\Documents\MyDB.accdb];

//-------- Start Multiple Select Statements ------

SQL SELECT *

FROM `Eye Colors`;

SQL SELECT *

FROM People;

//-------- End Multiple Select Statements ------

Re: SQL and LOAD statement

amit_saini — Thu, 21 Aug 2014 18:03:17 GMT

Steve ,

Some like this????

LOAD

date(Create_Date) as Create_Date_AS_IS,

date(Create_Date, 'YYYY-MM') as Create_Date_YYYYMM,

date(Create_Date, 'DD-MM-YYYY') as Create_Date_DDMMYYYY

;

SQL SELECT

Create_Date

FROM

CustomerTable

WHERE

Create_Date > '2012-01-01 00:00:00';

Thanks,

Re: SQL and LOAD statement

Anonymous — Thu, 21 Aug 2014 18:08:03 GMT

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO [MS Access Database;DBQ=C:\Users\szadroga\Documents\MyDB.accdb];

//-------- Start Multiple Select Statements ------

Load *;

SQL SELECT *

FROM `Eye Colors`;

Load *,

First_Name &' '& Second_Name as [Full Name]

;

SQL SELECT *

FROM People;

//-------- End Multiple Select Statements ------

Re: SQL and LOAD statement

Thu, 21 Aug 2014 18:19:11 GMT

thanks! worked perfectly, just needed to move code to the People table.

Re: SQL and LOAD statement

Thu, 21 Aug 2014 18:21:48 GMT

Hi,

Plesae check with this..

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Products: // This is the Table to store data in Qlikview

LOAD *;

SQL SELECT *

FROM AdventureWorksDW2008R2.dbo.DimCurrency;

Syntax:-

<TableName>:

LOAD <columns>;

<SQL Statiment>;

I hope this will helpful for you...

@Sub2u

Re: SQL and LOAD statement

Anonymous — Thu, 21 Aug 2014 18:32:30 GMT

glad to help.