Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
May be like 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;
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.
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;
Even this works.Thanks a lot:)