Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shmpradeep
Contributor II
Contributor II

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
shmpradeep
Contributor II
Contributor II
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,

......

shmpradeep
Contributor II
Contributor II
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.

shmpradeep
Contributor II
Contributor II
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));