Automation of Qlikview Scripting-field names mapping from an excel/DB to Standard project specific field names

[Pulkit_Thukral]

Let's assume we have an excel file as the source which has 8 fields(Headers) in it and we have to load this data in a pre-developed qvw application with ~80 default fields listed (Name of the actual excel field ,renamed as default field using "as" keyword.

What we have: - a person would write mapping details in a separate excel mapping fields from source excel to Default field name in QVW.

Complete process would require 2 steps:-

1.Script should automatically place "null() as 'Defailt_field_name'" if incase that field is not available in Source Dump or it can delete this line in the scripting.

2.With help of Mapping excel , script should be written using proper mapping.

Example:-

Source Excel:-

Number	Created_at	Closed_at	SLA	Time
123	2/14/2017	2/14/2017	Met	5:05:22 PM
456	2/14/2017	2/14/2017	Not-Met	5:07:22 PM

Mapping Excel:-

Source Excel	Standard Application Field Name
Number	Ticket ID
Created_at	Ticket_Submitted_At
Closed_at	Ticket_Closed_At
SLA	SLA_Met
Time	Ticket_Submit_Time
	Priority

Output to be achieved with AUTOMATION:-

Load

Number as Ticket ID,

Created_at as Ticket_Submitted_at,

Closed_At as Ticket_Closed_at,

SLA as SLA_Met ,

Time as Ticket_Submit_Time

null() as Priority or removal of this line if possible.

from source_excel.csv;

[sunny_talwar]

May be like this

How to Rename Fields using Mapping Table

[sunny_talwar · Accepted solution]

FieldMapping:

Mapping

LOAD * INLINE [

    Source Excel, Standard Application Field Name

    Number, Ticket ID

    Created_at, Ticket_Submitted_At

    Closed_at, Ticket_Closed_At

    SLA, SLA_Met

    Time, Ticket_Submit_Time

    Priority

];

Table:

LOAD * INLINE [

    Number, Created_at, Closed_at, SLA, Time

    123, 2/14/2017, 2/14/2017, Met, 5:05:22 PM

    456, 2/14/2017, 2/14/2017, Not-Met, 5:07:22 PM

];

RENAME Fields using FieldMapping;

[Pulkit_Thukral]

That is correct !

Thank you.

Also, will there be any possibility to include null() as default field value as well in above scripting wherever source is not present so that at end of this script ,fixed number of fields should be available always.

[sunny_talwar]

Try this

FieldMapping:

Mapping

LOAD * INLINE [

    Source Excel, Standard Application Field Name

    Number, Ticket ID

    Created_at, Ticket_Submitted_At

    Closed_at, Ticket_Closed_At

    SLA, SLA_Met

    Time, Ticket_Submit_Time

    ,Priority

];

Table:

LOAD * INLINE [

    Number, Created_at, Closed_at, SLA, Time

    123, 2/14/2017, 2/14/2017, Met, 5:05:22 PM

    456, 2/14/2017, 2/14/2017, Not-Met, 5:07:22 PM

];

RENAME Fields using FieldMapping;

TEMP:

LOAD [Standard Application Field Name] as NullFields;

LOAD * INLINE [

    Source Excel, Standard Application Field Name

    Number, Ticket ID

    Created_at, Ticket_Submitted_At

    Closed_at, Ticket_Closed_At

    SLA, SLA_Met

    Time, Ticket_Submit_Time

    ,Priority

]

Where Len(Trim([Source Excel])) = 0;

FOR i = 1 to FieldValueCount('NullFields')

  LET vField = FieldValue('NullFields', $(i));

  Join (Table)

  LOAD Null() as [$(vField)]

  AutoGenerate 1;

NEXT;

DROP Table TEMP;

[Pulkit_Thukral]

Even this works.Thanks a lot:)