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

[Anil_Babu_Samineni · Accepted solution]

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

];

[Anonymous]

Thank you very much Anil.  I am loading the data from MSSQL server, not Inline.

[Anil_Babu_Samineni]

You can with MySQL as well after load statement ..

[Anonymous]

Thank you, i will try it out and advise accordingly.

Regards.

Chris

[Anonymous]

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?

[Anil_Babu_Samineni]

Preceding loads are unique fields, You need to use all field names from 2 preceding load.

[Anonymous]

Many thanks.  Please elaborate i am new to QlikView.

[Anil_Babu_Samineni]

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;

[Anonymous]

Thank you very much, i will try it and come  back to you.