topic Re: Loading Flat files data into Qlikview in QlikView

Loading Flat files data into Qlikview

prasad_dumbre — Mon, 22 May 2017 10:02:15 GMT

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.

Re: Loading Flat files data into Qlikview

Kushal_Chawda — Mon, 22 May 2017 13:17:28 GMT

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)
;

Re: Loading Flat files data into Qlikview

prasad_dumbre — Tue, 23 May 2017 05:21:12 GMT

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,

[Name of offsetting account],

[Name of offsetting account1],

TCurr,

[Value TranCurr],

[Total quantity],

UM,

Plnt,

Func,

PFAr,

Tr.Prt,

[Rvrsl ref.],

R1,

Item,

[Purchase order text],

Purch.Doc.,

[Total Quantity],

[Source Object],

OTy1,

ParAct,

ParCost.,

Pers.No.,

[Source object name],

Material,

Ref.,

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