Does It Require Incremental Load?

[vinod_nallapaneni]

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.

[vinieme12]

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

[Anil_Babu_Samineni]

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

[vinieme12]

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

[vinod_nallapaneni]

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.

[vinieme12]

word document with what?

[vinod_nallapaneni]

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.

[vinieme12]

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.