topic Re: Does It Require Incremental Load? in QlikView

Does It Require Incremental Load?

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

Hi all,

I have table like this. For every reload total sale value will be updated as below. Now I want to capture those totals and store in a separate field and generate one new report with that total values. Here's challenge it is not associate to date. Only when reload will happen it will be updated.

Does it require Incremental load? If yes please help me how to achieve it?

Yesterday's report Today's report

Thanks in advance,

Vinod.

Re: Does It Require Incremental Load?

Clever_Anjos — Sat, 10 Dec 2016 12:12:48 GMT

>>associate to date.

Probably your table/file has a data field right?

Re: Does It Require Incremental Load?

vinieme12 — Sat, 10 Dec 2016 14:28:02 GMT

Incremetal Reload is used to append new rows/modified data,

But in your case the same rows are being updated with new values so I think you basically want to track changes to your data.

So create an AsOFTable everyday you reload the data

Example:

Archive:

LOAD * INLINE [

Product,Quantity,Amount

A,2,3000

B,5,5000

c,6,6600

d,4,2000

e,1,500

];

AsOFTOTAL:

LOAD

Today() as AsOFDate,

Sum(Amount)

Resident Archive;

Store AsOFTOTAL into somepath\AsOFTOTAL.QVD

From the following day concatenate your AsOFTable the first generated QVD

Re: Does It Require Incremental Load?

Anil_Babu_Samineni — Sun, 11 Dec 2016 13:03:09 GMT

Yes, This should work for Today's date. What if, Suppose if we need to get full accumulate for Tomorrows data for that. In that, He want Today's Date and Tomorrows date

As i assume, He must need this

Todays:

LOAD * INLINE [

Product,Quantity,Amount

A,2,3000

B,5,5000

c,6,6600

d,4,2000

e,1,500

];

AsOFTOTAL:

LOAD

Today() as AsOFDate,

Sum(Amount) as Final

Resident Archive;

Store AsOFTOTAL into somepath\AsOFTOTAL.QVD

Output:

AsOFDate Final

11-12-2016 17100

Here, I have added this Data in DB. This will fetch next reload that is tomorrow's Date

Tomorrows:

LOAD * INLINE [

Product,Quantity,Amount

A,2,3000

B,5,5000

c,6,6600

d,4,2000

e,1,600 // Here, I've modified to 600 instead of 500 for Next reload update Data

];

Finally, It must be this

Output:

AsOFDate Final

11-12-2016 17100

12-12-2016 17200

Then, He might need to show comparison for each date by using Final and AsOFDate

Do we do same thing using loops / We required always concatenate with all dates

Note: All inputs are more gain knowledge nigher suits nor won't work. vinod.nallapaneni‌ confirm the same if you are looking this

Re: Does It Require Incremental Load?

vinieme12 — Sun, 11 Dec 2016 14:08:49 GMT

Hi Anil that's what I I've stated in my last line , that the total summary qvd generated should be concatenated everyday with the asoftotal for that day!!

Re: Does It Require Incremental Load?

Anil_Babu_Samineni — Sun, 11 Dec 2016 14:12:57 GMT

I've tried same thing as you provide. It is concatenate with Existing Directory. But, No luck. That is the cause i am posting here.

Do you mean always need to concatenate for each date. Can you describe more to get idea to do this.

Re: Does It Require Incremental Load?

vinod_nallapaneni — Mon, 12 Dec 2016 03:47:57 GMT

Hi Clever,

There is association of date field for that object (Pivot). For every reload I need to capture that totals and reload date. And I need to create another object (Line chart) by day wise total trend line.

Regards,

Vinod.

Re: Does It Require Incremental Load?

vinod_nallapaneni — Mon, 12 Dec 2016 03:53:33 GMT

Hello Vineeth,

Thanks for your reply.

As Anil said I want output like that. Can you please describe more?

Regards,

Vinod.

Re: Does It Require Incremental Load?

vinod_nallapaneni — Mon, 12 Dec 2016 03:55:16 GMT

Hi Anil,

Thanks for your reply,

Yes, I'm expecting output like this. Please share me if you got solution.

Regards,

Vinod.

Re: Does It Require Incremental Load?

Mon, 12 Dec 2016 06:13:20 GMT

No Need...!

Re: Does It Require Incremental Load?

vinieme12 — Mon, 12 Dec 2016 06:59:31 GMT

Do you have data for future dates in your data? or is it only added daily ?

When you run first time your script will be as below

Example:

Archive:

LOAD * INLINE [

Product,Quantity,Amount

A,2,3000

B,5,5000

c,6,6600

d,4,2000

e,1,500

];

AsOFTOTAL:

LOAD

Today() as AsOFDate,

Sum(Amount)

Resident Archive;

Store AsOFTOTAL into somepath\AsOFTOTAL.QVD

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

From the following day the script will be

Archive:

LOAD * INLINE [

Product,Quantity,Amount

A,2,3000

B,5,5000

c,6,6600

d,4,2000

e,1,500

];

AsOFTOTAL:

LOAD

Today() as AsOFDate,

Sum(Amount)

Resident Archive;

CONCATENATE:

LOAD * FROM

RESIDENT somepath\AsOFTOTAL.QVD

WHERE NOT EXISTS(AsOFDate); // We are using where not exists to avoid duplicate rows for same date incase you reload data // multiple times in the day

Store AsOFTOTAL into somepath\AsOFTOTAL.QVD; //Overwrite existing Asof TABLE

Re: Does It Require Incremental Load?

Anil_Babu_Samineni — Mon, 12 Dec 2016 07:52:01 GMT

this is not a make sense, We will do same thing by using For Each loop to reduce the Every time reload. Hope so some body can help you regarding For Each script.

marcowedel‌ stalwar1‌ cleveranjos‌ gwassenaar‌ jagan‌

I am using this

Yesterday:

LOAD * INLINE [

Product,Quantity,Amount

A,2,3000

B,5,5000

c,6,6600

d,4,2000

e,1,500

];

AsOFTOTAL:

LOAD

Date(Num(Today()-1)) as AsOFDate,

Sum(Amount) as FinalAmount

Resident Yesterday;

Store AsOFTOTAL into C:\Users\asamineni\Downloads\AsOFTOTAL.QVD;

DROP Table Yesterday,AsOFTOTAL;

LOAD AsOFDate,

FinalAmount

FROM

(qvd);

Todays:

LOAD * INLINE [

Product,Quantity,Amount

A,2,3000

B,5,5000

c,6,6600

d,4,2000

e,1,600

];

AsOFTOTAL:

LOAD

Date(Num(Today())) as AsOFDate,

Sum(Amount) as FinalAmount

Resident Todays;

Store AsOFTOTAL into C:\Users\asamineni\Downloads\AsOFTOTAL.QVD;

I got output like below

Re: Does It Require Incremental Load?

vinieme12 — Mon, 12 Dec 2016 08:18:12 GMT

Anil, I already know LOOPING! But my reply is in context that data that is modified daily and those changes are that needs to be captured.

You response is in context that data already exists for each date! there is a difference

Re: Does It Require Incremental Load?

vinod_nallapaneni — Tue, 13 Dec 2016 09:42:47 GMT

Hello Vineeth,

No I don't have data for future dates. It is added daily.

can you please help me on this. If possible send me Microsoft word document?

Vinod.

Re: Does It Require Incremental Load?

vinieme12 — Tue, 13 Dec 2016 09:59:46 GMT

word document with what?

Re: Does It Require Incremental Load?

vinod_nallapaneni — Tue, 13 Dec 2016 10:05:01 GMT

Currently I'm using QV 9.0 So, If you post the sample QVW also I may not access. That's why I'm asking word document with code and screenshots as well.

Re: Does It Require Incremental Load?

vinieme12 — Tue, 13 Dec 2016 10:07:33 GMT

You can use the code posted above just make changes to the same .

What screenshots are you expecting? this is to be done in script editor.