topic Re: Using Preceding load for this example in QlikView

Using Preceding load for this example

zied_ahmed1 — Wed, 03 May 2017 07:58:38 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: Using Preceding load for this example

zied_ahmed1 — Wed, 03 May 2017 07:59:06 GMT

rahulpawarb‌ stalwar1‌

Re: Using Preceding load for this example

prieper — Wed, 03 May 2017 08:09:08 GMT

... and what is your question/problem?

Re: Using Preceding load for this example

zied_ahmed1 — Wed, 03 May 2017 08:12:02 GMT

I need to use Preceding load to load one time the data and display the three STATUTs without repeating the same code

Re: Using Preceding load for this example

tamilarasu — Wed, 03 May 2017 08:22:59 GMT

Hi Zied,

Why do you need preceding load? You can simply filter the data in where clause itself like below.

Let vToday = Today(); // Make sure the date format is correct

Datas:

load

date,

car as car,

mat ,

Tax,

'ON' as STATUT,

count(Statut_sup) as nbr

resident table1

where Statut_sup='Actif' And date = '$(vToday)'

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' And date = '$(vToday)'

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' And date = '$(vToday)'

group by date,

car,

mat;

drop table table1;

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

Concatenate

Load *

resident Fact ;

drop table Fact;

Re: Using Preceding load for this example

zied_ahmed1 — Wed, 03 May 2017 08:51:25 GMT

Hello Tamil,

I need to minimize the code, if you can see the three STATUT with concatenate i think i can use them in just one small code what do you think ?

Re: Using Preceding load for this example

vinieme12 — Wed, 03 May 2017 09:01:03 GMT

i don't even understand why we are concatenating three tables? this should be enuf

load

date,

car as car,

mat ,

Tax,

Pick(Wildmatch(STATUT,'Actif','Inactif','Bloquée'),'ON','OFF','Bloquée') as STATUT

count(Statut_sup) as nbr

resident table1

group by

date_flotte,

Taux,

//[%Date ID],

car ,

mat;

temp:

load

date,

car as car,

mat ,

Tax,

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

resident table1;

drop table table1;

noconcatenate

Load

date,

car,

mat ,

Tax,

STATUT

Count(STATUT) as nbr

Resident temp

Group by

date,

car,

mat ,

Tax,

STATUT;

Re: Using Preceding load for this example

tamilarasu — Wed, 03 May 2017 09:06:22 GMT

Zied - You are using different field names in first group by clause also "Tax" Field is missing in second and third load. and you are adding "Pla" field in second load. If you could confirm the field names are same in three loads and group by clause field names are same then we can try to simply the code.

Re: Using Preceding load for this example

zied_ahmed1 — Wed, 03 May 2017 09:12:17 GMT

Hello Tamil,

Yes i can use the same group by for the three load

Regards

Re: Using Preceding load for this example

zied_ahmed1 — Wed, 03 May 2017 09:22:01 GMT

Hello Vineeth,

I used the first code script but the error was with the STATUT :

Pick(Wildmatch(STATUT,'Actif','Inactif','Bloquée'),'ON','OFF','Bloquée') as STATUT

i think because we use it before we declare it ...

Re: Using Preceding load for this example

tamilarasu — Wed, 03 May 2017 09:27:40 GMT

Zied,

Here is a small method. You can alter the group by clause and the field names as per your need.

For Each Status in 'Actif', 'Inactif', 'Bloquée'

Result:
Load date,
car as car,
mat,
count(Statut_sup) as nbr,
Pick(Wildmatch(Statut_sup,'Actif','Inactif','Bloquée'),'ON','OFF','Bloquée') as STATUT
Resident table1
Where Statut_sup = '$(Status)' And Date = Today()
Group by date, car, mat;

Next Status

DROP Table table1;

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

Concatenate
Load *
resident Fact ;

drop table Fact;

Re: Using Preceding load for this example

Anil_Babu_Samineni — Wed, 03 May 2017 09:28:04 GMT

May be Field name misplaced STATUT instead of Statut_sup

Pick(Wildmatch(STATUTStatut_sup,'Actif','Inactif','Bloquée'),'ON','OFF','Bloquée') as STATUT