Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Pulkit_Thukral
Partner
Partner

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

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:-

     

NumberCreated_atClosed_atSLATime
1232/14/20172/14/2017Met5:05:22 PM
4562/14/20172/14/2017Not-Met5:07:22 PM

Mapping Excel:-

  

Source ExcelStandard Application Field Name
NumberTicket ID
Created_atTicket_Submitted_At
Closed_atTicket_Closed_At
SLASLA_Met
TimeTicket_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;

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

5 Replies
sunny_talwar

sunny_talwar

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;

View solution in original post

Pulkit_Thukral
Partner
Partner
Author

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
Partner
Partner
Author

Even this works.Thanks a lot:)