topic Re: How to retrieve data from 4 last week script loop ? in QlikView

How to retrieve data from 4 last week script loop ?

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

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.

PLease help !!!!

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

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

Re: How to retrieve data from 4 last week script loop ?

jyothish8807 — Tue, 23 Oct 2018 05:39:52 GMT

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;

Laod

from <>

where Week(Date)> $(vLast4Week);

Store A into ABC.qvd;

Br,

Re: How to retrieve data from 4 last week script loop ?

Anil_Babu_Samineni — Tue, 23 Oct 2018 06:45:46 GMT

Do you have any Week Calculation?

Re: How to retrieve data from 4 last week script loop ?

NiceC — Tue, 23 Oct 2018 07:17:26 GMT

Hello Jyothish, thanks for your answer.

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

, adr_pays pays

, 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')

, adr_pays

;

thanks

Re: How to retrieve data from 4 last week script loop ?

NiceC — Tue, 23 Oct 2018 07:24:28 GMT

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

, adr_pays pays

, 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')

, adr_pays

;

thanks

Re: How to retrieve data from 4 last week script loop ?

jyothish8807 — Tue, 23 Oct 2018 07:47:30 GMT

Try like this:

Load

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

AutoGenerate(5)

While IterNo()<5 ;

For i=0 to 3

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

Load

//Your statement

where Week(Date) = $(vWeek);

store B into QVD_$(vWeek).qvd;

next i

Br,

Re: How to retrieve data from 4 last week script loop ?

NiceC — Wed, 24 Oct 2018 14:13:23 GMT

Thanks Jyothish

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

Re: How to retrieve data from 4 last week script loop ?

jyothish8807 — Thu, 25 Oct 2018 05:24:52 GMT

Great , Glad it helped you.

Br,