topic Re: Derived Field in QlikView

Derived Field

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Derived Field

Anil_Babu_Samineni — Fri, 20 Oct 2017 09:58:22 GMT

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

];

Re: Derived Field

Anonymous — Fri, 20 Oct 2017 10:07:06 GMT

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

Re: Derived Field

Anil_Babu_Samineni — Fri, 20 Oct 2017 10:09:07 GMT

You can with MySQL as well after load statement ..

Re: Derived Field

Anonymous — Fri, 20 Oct 2017 10:23:23 GMT

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

Regards.

Chris

Re: Derived Field

Anonymous — Fri, 20 Oct 2017 13:12:06 GMT

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?

Re: Derived Field

Anil_Babu_Samineni — Fri, 20 Oct 2017 13:16:32 GMT

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

Re: Derived Field

Anonymous — Fri, 20 Oct 2017 13:23:29 GMT

Many thanks. Please elaborate i am new to QlikView.

Re: Derived Field

Anil_Babu_Samineni — Fri, 20 Oct 2017 13:26:08 GMT

Here we go, may be?

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;

Re: Derived Field

Anonymous — Fri, 20 Oct 2017 13:39:20 GMT

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

Re: Derived Field

Anonymous — Fri, 20 Oct 2017 20:19:01 GMT

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;

Re: Derived Field

Anil_Babu_Samineni — Sat, 21 Oct 2017 12:58:01 GMT

Welcome, glad it was working for you