Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Loading Flat files data into Qlikview

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.

2 Replies

Re: Loading Flat files data into Qlikview

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)

;

Partner
Partner

Re: Loading Flat files data into Qlikview

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