topic Re: loop data for the 4 last week in QlikView

loop data for the 4 last week

NiceC — Mon, 22 Oct 2018 09:33:07 GMT

Hi,

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: loop data for the 4 last week

sunny_talwar — Tue, 23 Oct 2018 00:26:52 GMT

Hi would you be able to share some data with which you are trying to run this script? It might help to know what you are getting and what are you expecting to get based on the data shared

Re: loop data for the 4 last week

NiceC — Tue, 23 Oct 2018 07:13:22 GMT

Sunny, i want to get the last 4 weeks data from this sql statement and store them in a qvd file per week.

This query extracts data for the day.

We are on week 43 right now.

From this sql query below,

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

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

;

And here is a result of this sql script.