topic Re: How to transform a Account Payable txt file in qlikview in QlikView

How to transform a Account Payable txt file in qlikview

tahreen371 — Wed, 25 Nov 2020 16:16:04 GMT

Hello All

Re: How to transform a Account Payable txt file in qlikview

tahreen371 — Wed, 30 Jan 2019 18:12:29 GMT

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..

Re: How to transform a Account Payable txt file in qlikview

Gysbert_Wassenaar — Wed, 30 Jan 2019 18:21:35 GMT

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

Re: How to transform a Account Payable txt file in qlikview

tincholiver — Wed, 30 Jan 2019 19:18:08 GMT

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

Re: How to transform a Account Payable txt file in qlikview

tahreen371 — Thu, 31 Jan 2019 17:37:00 GMT

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

Re: How to transform a Account Payable txt file in qlikview

tahreen371 — Sun, 03 Feb 2019 08:34:54 GMT

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

Re: How to transform a Account Payable txt file in qlikview

tincholiver — Sun, 03 Feb 2019 15:07:41 GMT

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.