topic Re: Automation of Qlikview Scripting-field names mapping from an excel/DB to Standard project specific field names in QlikView

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

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

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;

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

sunny_talwar — Wed, 05 Apr 2017 11:36:33 GMT

May be like this

How to Rename Fields using Mapping Table

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

sunny_talwar — Wed, 05 Apr 2017 11:38:51 GMT

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;

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

Pulkit_Thukral — Wed, 05 Apr 2017 12:11:00 GMT

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.

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

sunny_talwar — Wed, 05 Apr 2017 12:27:47 GMT

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;

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

Pulkit_Thukral — Wed, 05 Apr 2017 12:47:32 GMT

Even this works.Thanks a lot:)