Excel load script to MSSQL server load script

[Anonymous]

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

[#2 · Accepted solution]

QlikView Preceding Load takes care of it. As follows:

[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

;

SQL SELECT [Script Number],

           [Drug Name],

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

FROM yourMSQLSERver table;

[Anonymous]

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

Regards.

Chris

[#4]

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.

[Anonymous]

Hi Mario

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

Regards.

Chris

[#6]

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;

[Anonymous]

Hi Mario

Many thanks, i got it

Regards.