QV as an ETL tool

[Anonymous]

I am extracting data from a source and would want to do the following action before loading the data into QV:

1. If a field [Doctor Number] is Null an entry of '00000' should be entered into this field value and;
2. If a field [Med Aid] is Null an entry of 'UNKNOWN' should be entered into this field's value.

How can this be accomplished in QV before/during the loading process?

Regards.

[swuehl]

LOAD

     If(Len(Trim([Doctor Number])), [Doctor Number], '00000') AS [Doctor Number],

     If(Len(Trim([Med Aid])), [Med Aid], 'UNKNONW') AS [Med Aid],

     ...

FROM ...;

[sunny_talwar · Accepted solution]

May be something like this:

LOAD If(Len(Trim([Doctor Number])) = 0, '00000', [Doctor Number]) as [Doctor Number],

          If(Len(Trim([Med Aid])) = 0, 'UNKNOWN', [Med Aid]) as [Med Aid

          OtherFields

FROM Source;

[Anonymous]

Thank you Swuehl

Your script seems not have a value for the Len function?

[HirisH_V7]

Hi,

LOAD  If(IsNull([Doctor Number]), '00000', [Doctor Number]) as [Doctor Number],
          If(IsNull([Med Aid]), 'UNKNOWN', [Med Aid]) as [Med Aid]

FROM Source;

HTH,

Hirish

[Anonymous]

Thanks Sunny, this should do the trick.  Confirm, from the script, if the [Doctor Number] or [Med Aid] are not null the existing field value is loaded?

Regards.

[sunny_talwar]

Yup. If they have values, they will be picked

[swuehl]

If Len() returns zero, the if() statement will execute the ELSE branch, if it returns a number, the THEN branch.

You don't need to check for zero explicitely, but you can do, if you want.

[Anonymous]

Thanks Hirish for your input, most appreciated.  Is there a difference from using the Len and Trim functions as per Sunny's recommendations on this forum?  Which is the most efficient if there are no differences?

Regards.

[Anonymous]

Ok thanks Swuehl