Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Im trying to load the data in my app but Im not getting desired results.
i have data in below format and each hour we have multiple files from source system are created. There is no primary key in the table. Hence i tried to use autonumber256(all the columns available ) to make a primary key during my extraction.
JUN|2018|PaymentGW-VD1|6|7|012678988|5
MAY|2018|PaymentGW-VD1|6|7|012678988|5
JUN|2018|PaymentGW-VD2|6|7|012678989|6
JUN|2018|PaymentGW-VD1|6|7|012678989|6
i tried to use the incremental load method ( Insert and Update ) but the only possibility which i want to avoid in my load is the avoid the duplicate.
In the subsequent loads i tried to load but i see the number of distinct rows reduced which should not be happening in this case because total number of rows in qvd should be increasing always.
my question is - what is the best way to load the data but to avoid duplicate rows only?
below is my extraction code :
V_ACTTRANS_TAB:
LOAD Autonumberhash256(MONTH,YEAR,GW,NBRTYPE,GWTYPE,NUMBER,OPERATOR) as primarykey,
MONTH,
YEAR,
GW,
NBRTYPE,
GWTYPE,
NUMBER,
OPERATOR
FROM
[$(vrawfilepath)\*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes) ;
concatenate
Load
primarykey,
MONTH,
YEAR,
GW,
NBRTYPE,
GWTYPE,
NUMBER,
OPERATOR
FROM
ACTTRANS_TAB.QVD (qvd)
where (not exists (primarykey));
I'm able to fix the problem as below after modification of my primary key names. Needed to understand the exists function first to make it clear to me.
It is very important to understand the intricacies of your data and functions which we want to apply.
V_ACTTRANS_TAB:
LOAD Autonumberhash256(MONTH,YEAR,GW,NBRTYPE,GWTYPE,NUMBER,OPERATOR) as primarykey,
MONTH,
YEAR,
GW,
NBRTYPE,
GWTYPE,
NUMBER,
OPERATOR
FROM
[$(vrawfilepath)\*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes) ;
concatenate
Load
primarykey as PKEY,
MONTH,
YEAR,
GW,
NBRTYPE,
GWTYPE,
NUMBER,
OPERATOR
FROM
ACTTRANS_TAB.QVD (qvd)
where (not exists (PKEY,primarykey));
Hi,
you can try using rowno() and maybe create a key with filename and rowno().
load
rowno() &'-'&Filename() as key,
Filename() as Filename,
rowno() as ID,
......
HI,
filenames are like YYYYMMDDHH.txt which is creating unique files per hours and if the key is the sequence between for each record in the file, i think the above criteria will not work.
I'm able to fix the problem as below after modification of my primary key names. Needed to understand the exists function first to make it clear to me.
It is very important to understand the intricacies of your data and functions which we want to apply.
V_ACTTRANS_TAB:
LOAD Autonumberhash256(MONTH,YEAR,GW,NBRTYPE,GWTYPE,NUMBER,OPERATOR) as primarykey,
MONTH,
YEAR,
GW,
NBRTYPE,
GWTYPE,
NUMBER,
OPERATOR
FROM
[$(vrawfilepath)\*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes) ;
concatenate
Load
primarykey as PKEY,
MONTH,
YEAR,
GW,
NBRTYPE,
GWTYPE,
NUMBER,
OPERATOR
FROM
ACTTRANS_TAB.QVD (qvd)
where (not exists (PKEY,primarykey));