topic Re: loading issue qlikview in QlikView

loading issue qlikview

Anonymous — Thu, 21 Jun 2018 08:44:48 GMT

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

Re: loading issue qlikview

captain89 — Thu, 21 Jun 2018 15:47:53 GMT

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,

......

Re: loading issue qlikview

Anonymous — Fri, 22 Jun 2018 08:10:18 GMT

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.

Re: loading issue qlikview

Anonymous — Tue, 03 Jul 2018 04:53:33 GMT

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