topic Re: incremental load on txt file without date field. in QlikView

incremental load on txt file without date field.

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

hello everyone

my txt file looks like this and link is here as they are not allowing me to attach txt file.

https://drive.google.com/file/d/1DlQW8Sc4h0JRmLu5diIgsV8b4lH1dhIr/view?usp=sharing

use delimater as this | pipe sign

please help me to do incremental and update the records on the basis of ean which is the column after MGRP4DESC column.

please help me i am in need.

i searched everywhere but didnt got solution.

Re: incremental load on txt file without date field.

dplr-rn — Thu, 22 Aug 2019 14:18:12 GMT

For incremental load you need to have at least one of below

1) Last updated date or any date field

2) Sequential ID

If EAN is a sequential one you can use that.

Why do you need to use incremental load here? Can you describe the scenario a bit

Re: incremental load on txt file without date field.

dplr-rn — Thu, 22 Aug 2019 14:20:22 GMT

correction to above. I saw you want to do update as well. for insert and update you need a date field.

As i said describe scenario a bit more

Re: incremental load on txt file without date field.

riishabhz — Fri, 23 Aug 2019 07:25:08 GMT

sir its a master table and everyday its updated like if today certain item has mrp of 500rs then tomorrow it can be changed to 700rs so i have to update that value.

moreover every day a new master is created .

Re: incremental load on txt file without date field.

dplr-rn — Fri, 23 Aug 2019 11:18:44 GMT

Does the new master file hold all the records? Or just new and updated ones?

Re: incremental load on txt file without date field.

riishabhz — Tue, 27 Aug 2019 04:18:38 GMT

new records and updated records.

like todays master will have certain mrp like 500 and after 2 days that master will be updated to another price and so on

Re: incremental load on txt file without date field.

dplr-rn — Tue, 27 Aug 2019 11:58:59 GMT

Few questions

1-does it contain only new and updated records?

2-How long does reload take?

Re: incremental load on txt file without date field.

riishabhz — Tue, 27 Aug 2019 12:16:39 GMT

yes it contain new record daily , sometimes price is also updated.

master file is small in size like 10-12mb so it wont take long to load the data.

Re: incremental load on txt file without date field.

dplr-rn — Tue, 27 Aug 2019 14:03:01 GMT

So the file will contain only new and updated records .

if so the question for you is do you have a unique id for a row. if you have you can easily do the incremental load . follow instructions from below link from insert update section

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

if the row doesnt have an 'ID' you will need to create a unique identifier in someway using hash key or something

Re: incremental load on txt file without date field.

riishabhz — Tue, 27 Aug 2019 15:06:44 GMT

Sir i AM new to qlikview please help me, i have also attached link of
Google drive where you can find the data, please look at it.

Re: incremental load on txt file without date field.

dplr-rn — Tue, 27 Aug 2019 15:10:01 GMT

My friend i am at a full time job. But i am trying to help but unfortunately i cannot write the whole thing for you.

For your learning i would suggest you try the approach suggested. Where you are going wrong i will be able to help.

can you answer my questions from previous reply

Re: incremental load on txt file without date field.

rwunderlich — Tue, 27 Aug 2019 15:31:18 GMT

How about:

Master:
LOAD *
FROM
[..\Downloads\MasterFile.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq);

Concatenate (Master)
LOAD *
From Master.qvd (qvd)
Where not exists(EAN)
;

STORE Master into Master.qvd (qvd);

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: incremental load on txt file without date field.

riishabhz — Tue, 27 Aug 2019 16:42:45 GMT

Ok sir thanx will try tomorrow morning and will update you 🙂

Re: incremental load on txt file without date field.

riishabhz — Wed, 28 Aug 2019 11:07:49 GMT

yes sir its working but its not updating the value .

like this example

ean | price

1|10

1|20

qlikview should show ean 1 price as 20 because its updated value, but instead of updating it is adding and showing 30

what should i do now?

Re: incremental load on txt file without date field.

rwunderlich — Wed, 28 Aug 2019 18:12:38 GMT

Did you include the:

Where not exists(EAN)

Can you post your script?

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: incremental load on txt file without date field.

riishabhz — Wed, 28 Aug 2019 18:39:44 GMT

Yes sir i did include it, just same example that you gave.

Re: incremental load on txt file without date field.

riishabhz — Thu, 29 Aug 2019 10:43:40 GMT

here is my script:-
master:
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt
FROM
[..\material master and customer master txt\MAT_MAS_20190731_131746.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq) ;

Concatenate(master)
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt
FROM
[..\qvw file\qvd file\master1.qvd]
(qvd)Where not exists(EAN);

STORE master into [..\qvw file\qvd file\master1.qvd](qvd);

see photos , in excel i updated the value from 999 to 100 but qlikview is showing both the data , i want qlikview to show me 100 in data

Re: incremental load on txt file without date field.

rwunderlich — Thu, 29 Aug 2019 17:13:37 GMT

So your txt file has both the old and the new values for the same EAN? And you want the last value in the file?

-Rob

Re: incremental load on txt file without date field.

riishabhz — Thu, 29 Aug 2019 17:24:45 GMT

Yes sir, i want only new value to be shown in records

Re: incremental load on txt file without date field.

rwunderlich — Thu, 29 Aug 2019 17:56:14 GMT

master:
LOAD
*,
RecNo() as RecId
FROM
[..\material master and customer master txt\MAT_MAS_20190731_131746.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq) ;

Inner Join(master)
LOAD EAN, Max(RecId) as RecId
Resident master
Group by EAN
;
DROP FIELD RecId;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com