Renaming a field in the same load script

[diwaskarki]

Hello Guys,

My script looks like this:

SPT_AUDIT_EVENT:

[Removed Roles],

[Removed Entitlements]

FROM [..\..\QVD\MyTable.qvd] (qvd);

ASAR_Table:

Load

Trim([ASAR Removal Type]) As [Removal Type],

Trim([ASAR Event Trigger]) As [ASAR Trigger],

Trim([Removed Roles]) As [ASAR Roles],

Trim([Removed Entitlements]) As [ASAR Entitlements],

SubStringCount([ASAR Roles],'chr(10)') As roleCount1,

SubStringCount([ASAR Entitlements],'chr(10)') As EntCount1

Resident SPT_AUDIT_EVENT;

Whenever I run the script , it says it cant find [ASAR Roles]. Is it because I cant rename a field in the same table?

[vishsaggi · Accepted solution]

Yes you cannot rename the field in same load and use that renamed field in the same load. So try like resident load as below:

ASAR_Table:

LOAD *,

SubStringCount([ASAR Roles],'chr(10)') As roleCount1,

SubStringCount([ASAR Entitlements],'chr(10)') As EntCount1;

Load

Trim([ASAR Removal Type]) As [Removal Type],

Trim([ASAR Event Trigger]) As [ASAR Trigger],

Trim([Removed Roles]) As [ASAR Roles],

Trim([Removed Entitlements]) As [ASAR Entitlements]

Resident SPT_AUDIT_EVENT;

[PrashantSangle]

You can rename field in same table but you cannot use renamed field in same load.

So as suggested you can use preceding load  or resident load, or use original field name in new calculation

like

1: Preceding load

ASAR_Table:

LOAD *,

SubStringCount([ASAR Roles],'chr(10)') As roleCount1,

SubStringCount([ASAR Entitlements],'chr(10)') As EntCount1;

Load

Trim([ASAR Removal Type]) As [Removal Type],

Trim([ASAR Event Trigger]) As [ASAR Trigger],

Trim([Removed Roles]) As [ASAR Roles],

Trim([Removed Entitlements]) As [ASAR Entitlements]

Resident SPT_AUDIT_EVENT;

2: Using Resident Load

ASAR_Table:

Load

Trim([ASAR Removal Type]) As [Removal Type],

Trim([ASAR Event Trigger]) As [ASAR Trigger],

Trim([Removed Roles]) As [ASAR Roles],

Trim([Removed Entitlements]) As [ASAR Entitlements]

SubStringCount([ASAR Roles],'chr(10)') As roleCount1,

SubStringCount([ASAR Entitlements],'chr(10)') As EntCount1

Resident SPT_AUDIT_EVENT;

Noconcatenate

ASAR_Table_Final:

LOAD *,

SubStringCount([ASAR Roles],'chr(10)') As roleCount1,

SubStringCount([ASAR Entitlements],'chr(10)') As EntCount1;

Resident ASAR_Table;

3: Using same field in calculation

ASAR_Table:

Load

Trim([ASAR Removal Type]) As [Removal Type],

Trim([ASAR Event Trigger]) As [ASAR Trigger],

Trim([Removed Roles]) As [ASAR Roles],

Trim([Removed Entitlements]) As [ASAR Entitlements]

SubStringCount(Trim([Removed Roles]),'chr(10)') As roleCount1,

SubStringCount(Trim([Removed Entitlements]),'chr(10)') As EntCount1

Resident SPT_AUDIT_EVENT;

Regards,

[andrey_krylov]

It's because the field [ASAR Roles] doesn't exist in table [SPT_AUDIT_EVENT]. In other words, you are trying to LOAD [ASAR Roles] from [SPT_AUDIT_EVENT].

[diwaskarki]

Thanks . This makes sense.

[diwaskarki]

Thank you .