Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

loading issue qlikview

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

3 Replies
captain89
Creator
Creator

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,

......

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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