How to transform a Account Payable txt file in qlikview

[tahreen371]

Hello All 

[tahreen371]

Hello ALL,

An additional information to this is 

In addition:

Numbers in the files may be in either European or UK number format.

Plz consider Thanks..

[Gysbert_Wassenaar]

Personally I'd give this back to whoever produced this and tell him/her to stuff it somewhere the sun doesn't shine and give me a proper data set instead of this kind of rubbish output that's obviously meant for printing on dead trees.

Well, you've got a lot of cleaning up to do. This should at least load something that can be processed further:

data:
LOAD
[Alt Reference Number],
[Base Amount in F.Crcy],
[Business PartnerName],
BP,
City,
Clearing,
CoCd,
ISO,
Date,
[Deduct.Input Tax],
[Doc. Date],
DocumentNo,
Type,
Exch.rate,
[FC: Deductible],
[FC: Non-deduct.],
M,
Year,
FCrcy,
[G/L],
[Input tax],
[Input Tax in F. Crcy],
Country,
[Non-deductible],
PostalCode,
[Pstng Date],
[Receipt no.],
Type as Type2,
Curr.,
[Rnded.non-deduct],
[Rounded tax amt],
[Sequ. number],
Street,
[Tax acct],
[Tax base amount],
Tx,
Issuer,
Recipient,
Rate,
[Rep. Date],
[VAT Registration No.],
Vendor
FROM
[Accounts Payable.txt]
(txt, unicode, embedded labels, delimiter is '\t', no quotes, header is 6 lines)
WHERE CoCd = 'GB01';

[tincholiver]

You're right gysbert, that table is a disaster. I add some questions in the script, delete unnecessary fields and take the sequence number as id. The empty fields were eliminated in that way.

here the script:

Data:
LOAD
[Sequ. number],
Clearing,
CoCd,
ISO,
Date,
[Deduct.Input Tax],
[Doc. Date],
DocumentNo,
Type,
Exch.rate,
[FC: Deductible],
[FC: Non-deduct.],
M,
Year,
FCrcy,
[G/L],
[Input tax],
[Input Tax in F. Crcy],
Country,
[Non-deductible],
PostalCode,
[Pstng Date],
[Receipt no.],
Type1,
Curr.,
[Rnded.non-deduct],
[Rounded tax amt],
Street,
[Tax acct],
[Tax base amount],
Tx,
Issuer,
Recipient,
Rate,
[Rep. Date],
[VAT Registration No.],
Vendor
FROM
[$(Data)Accounts Payable.txt] (txt, unicode, embedded labels, delimiter is '\t', msq, filters(
Remove(Col, Pos(Top, 6)),Remove(Col, Pos(Top, 5)),Remove(Col, Pos(Top, 4)),Remove(Col, Pos(Top, 3)),Remove(Col, Pos(Top, 2)),Remove(Col, Pos(Top, 1)),Remove(Row, Pos(Top, 6)),Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4)),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1)) ))

Where [Sequ. number]>=1 and CoCd = 'GB01';

[tahreen371]

Hey Guys thank you both....................It worked

[tahreen371]

Hello Gysbert,

Could you please explain the steps on how you achieved this :[$(Data)Accounts Payable.txt] (txt, unicode, embedded labels, delimiter is '\t', msq, filters(
Remove(Col, Pos(Top, 6)),Remove(Col, Pos(Top, 5)),Remove(Col, Pos(Top, 4)),Remove(Col, Pos(Top, 3)),Remove(Col, Pos(Top, 2)),Remove(Col, Pos(Top, 1)),Remove(Row, Pos(Top, 6)),Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4)),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1)) ))

Like where are we suppose to enter the condition.

thanks in advance 

[tincholiver]

Hi Tahreen,

two ways to do this:

First one and easier:

1. Header size: 6 lines

2. Labels: Embedded

3. Delete this empty column

4. Finish....

Your script will look like this:

Payments:
LOAD
[Sequ. number],
[Alt Reference Number],
[Base Amount in F.Crcy],
[Business PartnerName],
BP,
City,
Clearing,
CoCd,
ISO,
Date,
[Deduct.Input Tax],
[Doc. Date],
DocumentNo,
//Type, // Take care, you have two fields with the same name
Exch.rate,
[FC: Deductible],
[FC: Non-deduct.],
M,
Year,
FCrcy,
[G/L],
[Input tax],
[Input Tax in F. Crcy],
Country,
[Non-deductible],
PostalCode,
[Pstng Date],
[Receipt no.],
Type,
Curr.,
[Rnded.non-deduct],
[Rounded tax amt],
Street,
[Tax acct],
[Tax base amount],
Tx,
Issuer,
Recipient,
Rate,
[Rep. Date],
[VAT Registration No.],
Vendor    

FROM [..\..\Accounts Payable.txt]    (txt, unicode, embedded labels, delimiter is '\t', msq, header is 6 lines)

//Where condition to reload only valuables records

Where [Sequ. number]>=1 and CoCd = 'GB01';

Run script.

Second way:

1. clik next in firts window

2. clik Enable transformation step,

3. Remove manually each row and columns that you dont need, clik next, next and finish.

Now your script will look like this:

LOAD
[Sequ. number],
Clearing,
CoCd,
ISO,
Date,
[Deduct.Input Tax],
[Doc. Date],
DocumentNo,
Type,
Exch.rate,
[FC: Deductible],
[FC: Non-deduct.],
M,
Year,
FCrcy,
[G/L],
[Input tax],
[Input Tax in F. Crcy],
Country,
[Non-deductible],
PostalCode,
[Pstng Date],
[Receipt no.],
Type1,
Curr.,
[Rnded.non-deduct],
[Rounded tax amt],
Street,
[Tax acct],
[Tax base amount],
Tx,
Issuer,
Recipient,
Rate,
[Rep. Date],
[VAT Registration No.],
Vendor
FROM
[C:\Users\Wicham\Desktop\Accounts Payable.txt] (txt, unicode, embedded labels, delimiter is '\t', msq, filters(
Remove(Col, Pos(Top, 6)),Remove(Col, Pos(Top, 5)),Remove(Col, Pos(Top, 4)),Remove(Col, Pos(Top, 3)),Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 1)),Remove(Row, Pos(Top, 6)),Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4)),Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1)) )) Where [Sequ. number]>=1 and CoCd = 'GB01';;

Same result in any case.