Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following fields and values in my fact table:
MedAidAmt | Shortfall |
---|---|
15 | 2 |
5 | 0.50 |
0 | 10 |
0 | 20 |
I want to create two fields based on the value of the MedAidAmt at load script level as follows:
If the MedAidAmt is = 0, the Shortfall field becomes FullPMT (full payment as this is not a shortfall), otherwise it becomes PartPMT (part payment). The table below shows the results to be accomplished:
MedAidAmt | Shortfall | FullPMT | PartPMT |
---|---|---|---|
15 | 2 | 0 | 2 |
5 | 0.50 | 0 | 0.5 |
0 | 10 | 10 | 0 |
0 | 20 | 20 | 0 |
How can i accomplish this at the load script level?
Regards.
Chris
Do you mean this?
LOAD MedAidAmt, Shortfall, FullPMT, If(FullPMT = 0, Shortfall, 0) as PartPMT;
LOAD *, If(MedAidAmt = 0, Shortfall, 0) as FullPMT Inline [
MedAidAmt, Shortfall
15, 2
5, 0.50
0, 10
0, 20
];
Do you mean this?
LOAD MedAidAmt, Shortfall, FullPMT, If(FullPMT = 0, Shortfall, 0) as PartPMT;
LOAD *, If(MedAidAmt = 0, Shortfall, 0) as FullPMT Inline [
MedAidAmt, Shortfall
15, 2
5, 0.50
0, 10
0, 20
];
Thank you very much Anil. I am loading the data from MSSQL server, not Inline.
You can with MySQL as well after load statement ..
Thank you, i will try it out and advise accordingly.
Regards.
Chris
I have tried the following load script and i get a load error field not found 'Script Number'
[Main Data]:
LOAD [Script Number],
[Drug Name],
Date(Floor([Date Dispensed])) as [Date Dispensed],
Year([Date Dispensed]) * 12 + Month([Date Dispensed]) as PeriodID,
Time(Interval([Date Dispensed],'hh:mm:ss'),'hh:mm:ss') as TRx_Time_Dispensed,
if([Script Number]=Previous([Script Number]),'',Time(Interval([Date Dispensed]-Previous([Date Dispensed])),'hh:mm:ss')) as TRx_Traffic,
DateKey,
[Repeat or Original],
If(Len(Trim([Doctor Name])) = 0, 'UNKOWN', [Doctor Name]) as Prescriber,
QTY*1 as QTY,
[MedAid Amount]*1 as [MedAid Amount],
Shortfall*1 as Shortfall,
RangeSum([MedAid Amount],[Shortfall])*1 as Amount,
If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', If(WildMatch([Med Aid], '*PRIVATE*'), 'OUT-OF-POCKET',[Med Aid])) as [Payment Type],
[Supply days],
ProductCode,
Num([Drug Code],'000000') as [Drug Code],
If(Len(Trim([Doctor Number])) = 0, '00000', If(Len(Trim([Doctor Number])) <= 4, Num([Doctor Number], '00000'), [Doctor Number])) as [Doctor Number],
Upper ([Retail Pharmacy]) as RETAILPHARMACY,
Upper(Branch) as BRANCH,
Date(Date_Modified) as Date_Modified;
LOAD [MedAid Amount], Shortfall, FullPMT, If(FullPMT = 0, Shortfall, 0) as PartPMT;
LOAD *, If([MedAid Amount] = 0, Shortfall, 0) as FullPMT;
SQL SELECT Branch,
DateKey,
"Date Dispensed",
"Date_Modified",
"Doctor Name",
"Doctor Number",
"Drug Code",
"Drug Name",
"Med Aid",
"MedAid Amount",
QTY,
"Repeat or Original",
"Retail Pharmacy",
"Script Number",
Shortfall,
ProductCode,
"Supply days"
FROM PharmaMarketAudit.dbo.tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2012-01-01' and [Date Dispensed] < '2017-10-01'
ORDER BY [Retail Pharmacy] asc, [Branch] asc, [Date Dispensed] asc;
Are the load statements from your suggestions correct?
Preceding loads are unique fields, You need to use all field names from 2 preceding load.
Many thanks. Please elaborate i am new to QlikView.
Here we go, may be?
[Main Data]:
LOAD [Script Number],
[Drug Name],
Date(Floor([Date Dispensed])) as [Date Dispensed],
Year([Date Dispensed]) * 12 + Month([Date Dispensed]) as PeriodID,
Time(Interval([Date Dispensed],'hh:mm:ss'),'hh:mm:ss') as TRx_Time_Dispensed,
if([Script Number]=Previous([Script Number]),'',Time(Interval([Date Dispensed]-Previous([Date Dispensed])),'hh:mm:ss')) as TRx_Traffic,
DateKey,
[Repeat or Original],
If(Len(Trim([Doctor Name])) = 0, 'UNKOWN', [Doctor Name]) as Prescriber,
QTY*1 as QTY,
[MedAid Amount]*1 as [MedAid Amount],
Shortfall*1 as Shortfall,
RangeSum([MedAid Amount],[Shortfall])*1 as Amount,
If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', If(WildMatch([Med Aid], '*PRIVATE*'), 'OUT-OF-POCKET',[Med Aid])) as [Payment Type],
[Supply days],
ProductCode,
Num([Drug Code],'000000') as [Drug Code],
If(Len(Trim([Doctor Number])) = 0, '00000', If(Len(Trim([Doctor Number])) <= 4, Num([Doctor Number], '00000'), [Doctor Number])) as [Doctor Number],
Upper ([Retail Pharmacy]) as RETAILPHARMACY,
Upper(Branch) as BRANCH,
Date(Date_Modified) as Date_Modified;
LOAD Branch,
DateKey,
"Date Dispensed",
"Date_Modified",
"Doctor Name",
"Doctor Number",
"Drug Code",
"Drug Name",
"Med Aid",
"MedAid Amount",
QTY,
"Repeat or Original",
"Retail Pharmacy",
"Script Number",
Shortfall,
ProductCode,
"Supply days",
FullPMT,
If(FullPMT = 0, Shortfall, 0) as PartPMT;
LOAD
Branch,
DateKey,
"Date Dispensed",
"Date_Modified",
"Doctor Name",
"Doctor Number",
"Drug Code",
"Drug Name",
"Med Aid",
"MedAid Amount",
QTY,
"Repeat or Original",
"Retail Pharmacy",
"Script Number",
Shortfall,
ProductCode,
"Supply days",
If([MedAid Amount] = 0, Shortfall, 0) as FullPMT;
SQL SELECT Branch,
DateKey,
"Date Dispensed",
"Date_Modified",
"Doctor Name",
"Doctor Number",
"Drug Code",
"Drug Name",
"Med Aid",
"MedAid Amount",
QTY,
"Repeat or Original",
"Retail Pharmacy",
"Script Number",
Shortfall,
ProductCode,
"Supply days"
FROM PharmaMarketAudit.dbo.tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2012-01-01' and [Date Dispensed] < '2017-10-01'
ORDER BY [Retail Pharmacy] asc, [Branch] asc, [Date Dispensed] asc;
Thank you very much, i will try it and come back to you.