topic Re: Renaming a field in the same load script in QlikView

Renaming a field in the same load script

diwaskarki — Thu, 16 Aug 2018 22:20:43 GMT

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?

Re: Renaming a field in the same load script

vishsaggi — Thu, 16 Aug 2018 22:41:19 GMT

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;

Re: Renaming a field in the same load script

PrashantSangle — Fri, 17 Aug 2018 07:01:48 GMT

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

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,

Re: Renaming a field in the same load script

andrey_krylov — Fri, 17 Aug 2018 07:32:58 GMT

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].

Re: Renaming a field in the same load script

diwaskarki — Tue, 21 Aug 2018 14:33:08 GMT

Thanks . This makes sense.

Re: Renaming a field in the same load script

diwaskarki — Tue, 21 Aug 2018 14:33:20 GMT

Thank you .