Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
cedfoning
New 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.

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

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
jyothish8807
Honored Contributor II

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

Try like this:

A:

Load

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

AutoGenerate(5)

While IterNo()<5 ;

For i=0 to 3

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

B:

Load

//Your statement

where Week(Date) = $(vWeek);

store B into QVD_$(vWeek).qvd;

next i

Br,

KC

Best Regards,
KC

View solution in original post

7 Replies
jyothish8807
Honored Contributor II

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

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

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

Do you have any Week Calculation?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
cedfoning
New Contributor

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

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

cedfoning
New Contributor

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

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

jyothish8807
Honored Contributor II

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

Try like this:

A:

Load

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

AutoGenerate(5)

While IterNo()<5 ;

For i=0 to 3

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

B:

Load

//Your statement

where Week(Date) = $(vWeek);

store B into QVD_$(vWeek).qvd;

next i

Br,

KC

Best Regards,
KC

View solution in original post

cedfoning
New Contributor

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

Thanks Jyothish

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

jyothish8807
Honored Contributor II

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

Great , Glad it helped you.

Br,

KC

Best Regards,
KC