Announcements
cancel
Showing results for
Did you mean:
Contributor

## How to retrieve data from 4 last week script loop ?

I would like to make a loop that will retrieve the data of the last 4 weeks via a sql request and store them in a qvd file per week.

Here is what a collegue did, but it doesn't work.

set jour_calcul = today();

let ann = year (\$(jour_calcul) - 45);

let ann_prec = \$(ann) - 1;

let mois = month (\$(jour_calcul) - 45);

// Chargt des fichiers

// -------------------

For j = 1 to 2

if j = 1 then

`` let sem_ref = \$(ann_prec) & '2'; let sem_enr = \$(ann) & '1'; let deb =\$(#mois); ``

else

`` let sem_ref = \$(ann) & '1'; let sem_enr = \$(ann) & '2'; if \$(#mois) < 7 then ``

let deb = 1;

else

`` let deb = \$(#mois) - 6; ``

end if

end if

For i = \$(#deb) to 6

set filename = SEGM_\$(sem_enr)_\$(i).qvd;

segm:

SQL request (just an example)

store segm into \$(filename) (qvd);

drop table segm;

next i

next j

1 Solution

Accepted Solutions
Master II

Try like this:

A:

week(Today())-IterNo() as Week

AutoGenerate(5)

While IterNo()<5 ;

For i=0 to 3

Let vWeek=peek('Week',\$(i),'A');

B:

where Week(Date) = \$(vWeek);

store B into QVD_\$(vWeek).qvd;

next i

Br,

KC

Best Regards,
KC
7 Replies
Master II

Hi cedric,

You can simply try like this:

For example if have a field called 'Date' in your database.

Script:

Let vLast4Week= Week(Today())-4;

A:

Laod

*

from <>

where Week(Date)> \$(vLast4Week);

Store A into ABC.qvd;

Br,

KC

Best Regards,
KC

Do you have any Week Calculation?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Contributor
Author

But i want to store data per week per file.

Actually we are on week 43.

i want to have data for :

week 42 stored in abc_42.qvd

week 41 stored in abc_41.qvd

week 40 stored in abc_40.qvd

week 39 stored in abc_39.qvd

here is the query used :

SELECT e.cli_id

, seg_rfm, seg_semestres

, 20182 semestre

, decode(reseau,'W','Web','Mag') Canal

, Sum(clients.conv.get_montant(dev_iso_tenue,'EUR',dat_tic,'\$\$',ca_ttc_dev)) CA_TTC_EUR

, Sum(nb_art) NB_ART

, Sum(Decode(Sign(ca_ttc_dev),1,1,0)) NB_CDE, sysdate dat_maj

FROM clients.tic_entete e

join clients.cli_fiche f on f.cli_id=e.cli_id

join mag_magasins m on m.magasin = e.magasin

join seg_rfm s on s.cli_id = f.cli_id and s.semestre = 20181

WHERE dat_vte between clients.seg.prem_jour(20182) AND promod.ansem_date(201831,7)

GROUP BY e.cli_id

, seg_rfm, seg_semestres

, decode(reseau,'W','Web','Mag')

;

thanks

Contributor
Author

Hello Anil,

i only have a date in my database.

i want to store data per week per file.

Actually we are on week 43.

i want to have data for :

week 42 stored in abc_42.qvd

week 41 stored in abc_41.qvd

week 40 stored in abc_40.qvd

week 39 stored in abc_39.qvd

here is the query used :

SELECT e.cli_id

, seg_rfm, seg_semestres

, 20182 semestre

, decode(reseau,'W','Web','Mag') Canal

, Sum(clients.conv.get_montant(dev_iso_tenue,'EUR',dat_tic,'\$\$',ca_ttc_dev)) CA_TTC_EUR

, Sum(nb_art) NB_ART

, Sum(Decode(Sign(ca_ttc_dev),1,1,0)) NB_CDE, sysdate dat_maj

FROM clients.tic_entete e

join clients.cli_fiche f on f.cli_id=e.cli_id

join mag_magasins m on m.magasin = e.magasin

join seg_rfm s on s.cli_id = f.cli_id and s.semestre = 20181

WHERE dat_vte between clients.seg.prem_jour(20182) AND promod.ansem_date(201831,7)

GROUP BY e.cli_id

, seg_rfm, seg_semestres

, decode(reseau,'W','Web','Mag')

;

thanks

Master II

Try like this:

A:

week(Today())-IterNo() as Week

AutoGenerate(5)

While IterNo()<5 ;

For i=0 to 3

Let vWeek=peek('Week',\$(i),'A');

B:

where Week(Date) = \$(vWeek);

store B into QVD_\$(vWeek).qvd;

next i

Br,

KC

Best Regards,
KC
Contributor
Author

Thanks Jyothish

i just replaced AutoGenerate(5) by AutoGenerate(1).

Master II

Great , Glad it helped you.

Br,

KC

Best Regards,
KC
Community Browser