topic Re: Use preceding load with group by in QlikView

Use preceding load with group by

zied_ahmed1 — Mon, 01 May 2017 16:43:29 GMT

I would like to use Preceding Load with this code :

LOAD

* WHERE "date"= Today() ;

load

date,

car as car,

mat ,

Tax,

'ON' as STATUT,

count(Statut_sup) as nbr

resident table1

where Statut_sup='Actif'

group by

date_flotte,

Taux,

//[%Date ID],

car ,

mat;

Concatenate

load

Pla,

date,

car as car,

mat,

'OFF' as STATUT,

count(Statut_sup) as nbr

resident table1

where Statut_sup='Inactif'

group by date,

car,

mat,

Pla;

Concatenate

load

date,

car as car,

mat,

'Bloquée' as STATUT,

count(Statut_sup) as nbr

resident table1

where Statut_sup='Bloquée'

group by date,

car,

mat;

drop table table1;

STORE [FlotteFinal] into [$(vQvdPath)\QVD_final.qvd](qvd);

Concatenate

Load*

resident Fact ;

drop table Fact;

Re: Use preceding load with group by

Anonymous — Mon, 01 May 2017 16:55:09 GMT

preceding load works only on 1 load....

if you use concatenate you have more than 1 load.

So this doesn't work. You'll have to use resident load.

Re: Use preceding load with group by

zied_ahmed1 — Mon, 01 May 2017 17:10:57 GMT

I was thinking that i can Load this one time :

car as car,

mat ,

Tax,

count(Statut_sup) as nbr

resident table1

where Statut_sup='Actif'

group by

date_flotte,

Taux,

//[%Date ID],

car ,

mat;

after that i add just this 'ON' as STATUT, 'OFF' as STATUT , 'Blocke' as STATUT

Re: Use preceding load with group by

Anonymous — Mon, 01 May 2017 17:15:47 GMT

does that work without preceding load???:

load

date,

car as car,

mat ,

Tax,

'ON' as STATUT,

count(Statut_sup) as nbr

resident table1

where Statut_sup='Actif'

group by

date_flotte,

Taux,

//[%Date ID],

car ,

mat;

Re: Use preceding load with group by

Digvijay_Singh — Mon, 01 May 2017 17:21:41 GMT

If your group by is same in all loads, you can check and update STATUT in one statement -

Pick(Match(Statut_sup, 'Actif','Inactif','Bloquée',),'ON','OFF','Blocke' ) as STATUT

Re: Use preceding load with group by

zied_ahmed1 — Mon, 01 May 2017 17:27:05 GMT

yes yes i copied it wrong date_flotte is date and Taux is Tax

Re: Use preceding load with group by

zied_ahmed1 — Mon, 01 May 2017 17:27:38 GMT

this is interesting...for the Load what i do exactly

Re: Use preceding load with group by

Anonymous — Mon, 01 May 2017 17:29:56 GMT

then it should work...

What's the problem then?, do you get an empty table?!?

(I don't know if your datefield is a dual() datefield and I would suggest always using today(1) instead of today() within script.)

Re: Use preceding load with group by

zied_ahmed1 — Mon, 01 May 2017 18:11:26 GMT

it's doesn't work like this :

Flotte4:

load

Plaque,

statut_actif,

date_flotte,

statut_inactif,

statut_bloque,

statut_endom,

//Statut_sup,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

Taux,

if (Statut_cond=1,'Actif',

(if (Statut_cond=2,'Bloquée',

(IF(Statut_cond=3,'Inactif'))))) as Statut_sup

Resident Flotte3;

drop table Flotte3;

//pour SR sans traversée

NoConcatenate

FlotteFinal:

//chargement de QVD qui contient les données des jours qui précedent

LOAD

//autonumber( date(date_dossier, 'DD/MM/YYYY'),'%Date ID') as [%Date ID],

date_flotte,

Plaque_flotte,

Materiel_flotte,

Taux,

Plaque FROM [$(vQvdPath)\Vectorys_final.qvd] (qvd) ;//source des QVDs historisés

Concatenate

//Concatenation du QVD avec les données d'ajourd'hui

LOAD

* WHERE "date_flotte"= Today();

// STORE [Test] into [$(vQvdPath)\Vectorys_Today.qvd](qvd);

load

Plaque,

date_flotte,

Plaque_flotte as Plaque_flotte,

Materiel_flotte,

Pick(Match(Statut_sup, 'Actif','Bloquée'),'ON','Bloquée' ) as STATUT

resident Flotte4

group by

Plaque,

date_flotte,

Plaque_flotte,

Materiel_flotte;

drop table Flotte4;

Re: Use preceding load with group by

Digvijay_Singh — Mon, 01 May 2017 18:34:12 GMT

I think you are still using preceding load with concatenate, which might be the reason for not working. What error you are receiving?

You may try

having "date_flotte"= Today() along with group by clause to get rid of preceding load, not sure though.

Re: Use preceding load with group by

zied_ahmed1 — Mon, 01 May 2017 18:40:51 GMT

When i use the latest code, I have this error:

error msg "Aggregation expressions required by group by clause but i don't know why because i use all fields in Load in the group by

Re: Use preceding load with group by

Digvijay_Singh — Mon, 01 May 2017 18:47:02 GMT

The error looks reasonable as there is no Sum,Min, Max, count being used to group values using group by fields. What exactly you want to aggregate?

Re: Use preceding load with group by

Digvijay_Singh — Mon, 01 May 2017 18:50:02 GMT

Pick(Match() is just a conditional statement, it will assign the correct value to STATUT based on Statut_sup value but 'group by' is used to aggregate(sum,avg, min, max, count) facts based on our business needs.

Re: Use preceding load with group by

zied_ahmed1 — Mon, 01 May 2017 19:02:20 GMT

ok...so this is not a solution for my question

Re: Use preceding load with group by

zied_ahmed1 — Tue, 02 May 2017 13:27:38 GMT

rahulpawarb‌

Re: Use preceding load with group by

Anonymous — Tue, 02 May 2017 17:21:53 GMT

If you uploaded a sample (it can be scrambled) then I could tell you what's the problem...

It's really hard without sample data...