topic Re: File Format Problem in QlikView

File Format Problem

Mon, 23 Jun 2014 09:59:50 GMT

Hi,

I have sample files format as attached. How can I upload in an efficient way in qlikview please. I am using qlikview personal edition.

Re: File Format Problem

Anonymous — Mon, 23 Jun 2014 10:08:42 GMT

Something like below,

Sep_Applications_Workflow:

LOAD Process_Code,

Process_Instance,

Workitem_Id,

Activity_Code,

Txn_Date,

State_Code,

Assigned_User_Id,

Assigned_Dept_Id,

Actioned_User_Id

FROM

Sep Applications Workflow.rpt

(txt, codepage is 1252, embedded labels, delimiter is ' ', msq, filters(

Remove(Row, Pos(Top, 2))

));

Sep_to_May_Applications:

LOAD Application_Id,

Total_Amount_Requested,

Originating_NSC,

Decision_Type,

Decision_Code,

Import_Channel_Id,

Total_Sanctioned_Amount,

Sanctioned_Term,

Immediate_Drawdown,

Declined_Letter_Sent,

Customer_Contacted,

Documents_Printed,

Date_Docs_Printed,

Documents_Followed_Up,

Documents_Received,

Documents_Checked_OK,

Application_Booked,

Documents_posted,

Funds_Issued,

Status_Id,

Diary_Date_Time,

Responsibility_Id,

Held_Conditions_Fulfilled,

Signing_Arrangements_Made,

PCC_Status,

Send_Docs_To_Home,

Premium_Client,

Product_Changed,

Originating_User_Id,

Source_ID,

Holding_NSC,

NAPS_Booked_Date,

NAPS_Booked_User_Id,

Application_Graded,

Processing_NSC,

CJA_Alimail_Sent,

NaasCJAPrev,

NaasCJANo,

Source,

Sanctioner_Id,

CJA_Manual_Diary,

SCC_To_Fulfil,

Input_Date_Time,

Lifecycle_Status,

App_Type,

Decision_Date_Time,

Secured,

Security_Issues,

Waiver_Selection,

Referred_in_Error,

Waiver_No_Date,

Refer_Group,

Rework_User_Id

FROM

Sep to May Applications.rpt

(txt, codepage is 1252, embedded labels, delimiter is ' ', msq, filters(

Remove(Row, Pos(Top, 2))

));

Re: File Format Problem

Gysbert_Wassenaar — Mon, 23 Jun 2014 10:16:58 GMT

Those files look like fixed records. You can use the File Wizard to create a load statement. Select Fixed Record as File Type and then click on the Analyze Fix Positions button to create the fields.

Make sure to check all the field separation positions. The Analyzer may not automatically detect every field correctly.

Re: File Format Problem

Mon, 23 Jun 2014 10:33:02 GMT

Hi Gysbert,

Thanks it worked and I can see the same correct colomn headings and data in the fileds. But in the script it shows as below. Would I have to write each field name in script e.g. @1:21 as Application_Id

Applicataions_Data;

LOAD

@1:21

Application_Id

@22:44

@45:60

@61:88

@89:106

@107:130

@131:146

@147:165

@166:186

@187:205

@206:223

@224:234

@235:278

@279:300

@301:319

@320:340

@341:359

@360:376

@377:389

@390:401

@402:456

@457:474

@475:500

@501:526

@527:537

@538:555

@556:570

@571:586

@587:606

@607:616

@617:628

@629:683

@684:703

@704:722

@723:737

@738:754

@755:807

@808:821

@822:876

@877:890

@891:901

@902:945

@946:962

@963:971

@972:1026

@1027:1034

@1035:1050

@1051:1067

@1068:1085

@1086:1152

@1153:n

FROM

(

fix, codepage is 1252, embedded labels);

Re: File Format Problem

Gysbert_Wassenaar — Mon, 23 Jun 2014 11:10:58 GMT

Yes, unfortunately you have to add the field names yourself after the File Wizard created the load statement. The wizard doesn't seem to pick the field names up even if you enable the option 'embedded labels'.

Re: File Format Problem

Mon, 23 Jun 2014 11:16:40 GMT

HI Gysbert,

Ok fine. Really appreciate your help. thanks