Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Mario thank you very much. I will try that and give you feedback.
Regards.
Chris
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.
Hi Mario
Many thanks for that. How do i do this when i have double quotes in the SQL select statement?
Regards.
Chris
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;
Hi Mario
Many thanks, i got it
Regards.