Derived Field

[Anonymous]

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

[Anonymous]

Hi Anil

I changed it to the following and it worked, thanks.

[Main Data]:
LOAD
      *,
      If(FullPMT = 0, Shortfall, 0) as PartPMT;

LOAD
*,
      If([MedAid Amount] = 0, Shortfall, 0) as FullPMT;

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;
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;
 

[Anil_Babu_Samineni]

Welcome, glad it was working for you