Loading Flat files data into Qlikview

[prasad_dumbre]

Dear Qlik Experts,

Please find attached sample data file, we need to load this data into Qlik. What would be the best way to do this ?

Kindly Advice.

Warmest Regards,
Prasad D.

[Kushal_Chawda]

Data:
first 1 LOAD Field
where trim(Field)<>'';
LOAD PurgeChar([---------------------------------],'-') as Field
FROM
[ALL_CJI3_HC_WBS_QLIK20170522_spool11.txt]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 11 lines)
;

FieldNames:
LOAD *, RowNo() as Sequence
where trim(FieldNames)<>'';
LOAD subfield(Field,'|') as FieldNames
Resident Data;

for i=1 to FieldValueCount('FieldNames')

let vFieldName = trim(FieldValue('FieldNames',$(i)));

Condition:
LOAD 'TextBetween(PurgeChar([---------------------------------],'&chr(39)&'-'&chr(39)&'),'&chr(39)&'|'&chr(39)&','&chr(39)&'|'&chr(39)&',$(i)) as [$(vFieldName)]' as Condition
AutoGenerate 1;

NEXT

Fields:
LOAD Concat(Condition,','&chr(10)) as Fields
where Flag=1;
LOAD *,if(SubField(Condition,'as',2) = Previous(SubField(Condition,'as',2)),0,1) as Flag
Resident Condition;

let vFields = Peek('Fields',0,'Fields');

DROP Tables Data,Condition,Fields,FieldNames;

Data:
LOAD *
WHERE len(trim(DocumentNo))>0 and trim(DocumentNo)<>'*' and trim(DocumentNo)<>'DocumentNo';
LOAD RecNo() as Rec,
     $(vFields)
FROM
[ALL_CJI3_HC_WBS_QLIK20170522_spool11.txt]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 11 lines)
;

[prasad_dumbre]

Hey Kushal,

Thanks a lot for your effort.

This code did worked well & gave result upto certain extent. Please also have a look on below code, shared by a colleague ; This seems to be simple & yet gives a desired result.

Below_Table:

LOAD

     DocumentNo,

     [Cost Elem.],

     Year,

     [WBS Element],

     Per,

     [Created on],

     [Val/COArea Crcy],

     COCr,

     [CO object name],

     [Cost element descr.],

     Object,

     [Postg Date],

     Name,

     [Material Description],

     [Document Header Text],

     BTra,

     OTy,

     [Doc. Date],

     [Partner object],

     [User Name],

     RefDocNo,

     [Value date],

     PrtObjClss,

     [Object type],

     Order,

     [Project Definition],

     PTy,

     [Cost element name],

     [CO partner object name],

     Offst.acct,

     O,

     [Name of offsetting account],

     [Name of offsetting account1],

     TCurr,

     [Value TranCurr],

     [Total quantity],

     UM,

     Plnt,

     Func,

     PFAr,

     Tr.Prt,

     R,

     [Rvrsl ref.],

     R1,

     Item,

     [Purchase order text],

     Purch.Doc.,

     [Total Quantity],

     [Source Object],

     OTy1,

     ParAct,

     ParCost.,

     Pers.No.,

     [Source object name],

     Material,

     Ref.,

     D,

     ObCur,

     PUM,

     [Source object type]

FROM

[ALL_CJI3_HC_WBS_QLIK20170522_spool11.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', header is 66 lines, no eof, filters(

Remove(Row, RowCnd(CellValue, 2, StrCnd(contain, '--'))),

Remove(Row, RowCnd(CellValue, 2, StrCnd(contain, '*'))),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

Cheers

Warmest Regards,
Prasad D. Dumbre