topic Re: Excel load script to MSSQL server load script in QlikView

Excel load script to MSSQL server load script

Anonymous — Sun, 28 Dec 2014 23:39:02 GMT

What is the MSSQL server load script equivalent for the following Excel load script:

[Main Data]:
LOAD [Script Number],
      [Drug Name],
     Date(Floor([Date Dispensed])) as DateDispensed,
     // Directions,
     [Repeat or Original],
    [Doctor Name] as Prescriber,
      QTY,
      [MedAid Amount],
      Shortfall,
      [Med Aid] as [Payment Type],
      [Supply days],
      //[Drug Code]
     Num([Drug Code],'000000') as [Drug Code],
      Num([Doctor Number],'00000') as [Doctor Number],
      [Retail Pharmacy],
      Branch,
    Date(Date_Modified) as Date_Modified
FROM
[$(vFolderSourceData)Datawarehouse_2.xlsx]
(ooxml, embedded labels, table is Datawarehouse);

I am not sure on the Date(Floor), Date(Date_Modified), Num([Drug Code], '000000') and Num([Doctor Number], '0000') conversion to MSSQL Server equivalent.

Thanking you in advance

Regards

Re: Excel load script to MSSQL server load script

Mon, 29 Dec 2014 00:07:09 GMT

QlikView Preceding Load takes care of it. As follows:

;

SQL SELECT [Script Number],

[Drug Name],

[Date Dispensed], ..blabla , [Drug Code], [Doctor Number]

FROM yourMSQLSERver table;

Re: Excel load script to MSSQL server load script

Anonymous — Mon, 29 Dec 2014 01:12:38 GMT

Mario thank you very much. I will try that and give you feedback.

Regards.

Chris

Re: Excel load script to MSSQL server load script

Mon, 29 Dec 2014 01:30:28 GMT

Just make sure to cast [Date Dispensed] as Date in the SQL expression as follows:

cast ( [Date Dispensed] as date) sometimes Database Servers returns dates in different formast (ie: internal numeric representation) etc., so QlikView would expect a MM/DD/YYYY format. that's what cast as date performs.

Re: Excel load script to MSSQL server load script

Anonymous — Mon, 29 Dec 2014 02:37:19 GMT

Hi Mario

Many thanks for that. How do i do this when i have double quotes in the SQL select statement?

Regards.

Chris

Re: Excel load script to MSSQL server load script

Mon, 29 Dec 2014 03:27:24 GMT

Please Clarify. The cast is a MsSQL Server function so in the SQL statement you should put it

like this:

...

   [Retail Pharmacy],
      Branch,
    Date(Date_Modified) as Date_Modified

;

SQL SELECT [Script Number],

[Drug Name],

cast([Date Dispensed] as date) as [Date Dispensed], ..blabla , [Drug Code], [Doctor Number]

FROM yourMSQLSERver table;

Re: Excel load script to MSSQL server load script

Anonymous — Mon, 29 Dec 2014 03:51:54 GMT

Hi Mario

Many thanks, i got it

Regards.