topic Re: source in QlikView

source

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

hi all,

I get data from a static source like erp kind application and finally we recieve the source as a flat file ex.txt. these static files are uploaded in QV..

but my requirement is few columns of these data needs to be updated or changed sometimes. I Will not be able to go back to my source.

any other ideas here. For example using an excel i could edit the values in QV

Re: source

its_anandrjs — Fri, 29 Apr 2016 07:35:54 GMT

Can you explain with an example for this issue or may be you can go with the Incremental load for this.

Regards,

Anand

Re: source

avinashelite — Fri, 29 Apr 2016 07:36:15 GMT

could you please share the sample data and what changes you need to make ?? so it will be helpful for us to suggest the solution

Re: source

MindaugasBacius — Fri, 29 Apr 2016 07:36:53 GMT

Remove, insert columns or make any other changes not in ex.txt but while loading the data in the QlikView.

Re: source

syukyo_zhu — Fri, 29 Apr 2016 07:44:57 GMT

Hi,

I don"t know how do you charge yours file txt in qlik, do you have a file per day or per week, do you have charge severals file or juste a single file.

Why don't you backup your file txt in the end of your script everyday. So if you need change value for some fields, you can modify your file txt backuped and recharge your data from these files modified.

Hope helpful.

Re: source

raadwiptec — Fri, 29 Apr 2016 07:49:50 GMT

I am looking for some kind of logic that can be implemented.

My Source is for ex Data.txt ----> This file gets uploaded into QV

----------> In QVi created a pivot chart.

For ex Data will be as below

Customer ¦ Transactions ¦ Amount ¦ References

10000121 ¦ 54656454545646 ¦ 23000.22 ¦ XFD656565

10000123 ¦ 54222233396646 ¦ 4523000.22 ¦ XFD656323

10000127 ¦ 58787899999946 ¦ 823000.22 ¦ XFD6565454

10000128 ¦ 54656548222246 ¦ 283000.22 ¦ XFD6564545

so here i need to edit certain values for References without touching data.txt file.
I will not be able to edit anything in the Data.txt going back also

Re: source

syukyo_zhu — Fri, 29 Apr 2016 07:59:31 GMT

Do you try to use a correlation table,

For example, your need change reference from XFD656565 to XFD656777 for today

28042016|XFD656565 ---> 28042016|XFD656777

Then you can use mapping/applymap to change your value in table target.

Re: source

raadwiptec — Fri, 29 Apr 2016 08:01:24 GMT

hi xia, can you give me more detail here..how to make correction table and how to link?

Re: source

syukyo_zhu — Fri, 29 Apr 2016 08:21:02 GMT

Sorry, i don't find here how can i attach file.

//if you want to change reference XFD656565 to XFD657777 juste for today and for all of your data

MappingSource:

mapping LOAD * INLINE [

Source, target

29042016|XFD656565,XFD657777

];

//if you want, you add id client or others informations in mappingsource, par example 29042016|10000121|XFD656565

//you can use inline or file excel

//then you can use applymap to change your value

Source:

load

Customer,

Transactions,

Amount,

applymap( 'MappingSource', Date(Today(), 'DDMMYYYY')&'|'&References, References) as References;

LOAD Customer,

Transactions,

Amount,

References

FROM

[.....\source.txt]

(txt, codepage is 1252, embedded labels, delimiter is ' ', msq);

Re: source

arasantorule — Fri, 29 Apr 2016 08:34:54 GMT

Hi,

I am thinking this way

If you know the definite list of values and their respective reference values, we can maintain them in an excel file.

Now ,Load the raw data from .txt or any file.

Load data from excel file with reference values.

Now use the applymap() function to add,edit,update or concatenation to the actual value.

Thanks.

Re: source

Fri, 29 Apr 2016 09:05:40 GMT

Hi Raad,

it's possible to set manually the table fields ,
just count the positions .

See the Example

Re: source

raadwiptec — Mon, 02 May 2016 07:10:20 GMT

hi paola. manual is not possible. it is a big file sometime changes are enormous

Re: source

Mon, 02 May 2016 08:04:16 GMT

Hi Raad,You have to do it only for headers!!!

Re: source

raadwiptec — Mon, 02 May 2016 08:29:14 GMT

hi xia,

i cannot have the same name references in the same load..

Re: source

syukyo_zhu — Mon, 02 May 2016 09:53:03 GMT

Hi,

i'm not sure understand well your said, But in my example i haven't same field.

If yes, you can do these

name it as reference _new,

then,

drop field reference from yourtable;

rename field reference _new to reference

so you can juste keep one field "reference" in your table target.

Re: source

raadwiptec — Tue, 03 May 2016 09:13:55 GMT

hi paola

yes its a pivot table for ex

10000121 ¦ 54656454545646 ¦ 23000.22 ¦ XFD656565 - As Xia said if it is changing XFD777777 then other values - 10000121 ¦ 54656454545646 ¦ 23000.22 ¦ should go under the new reference