Data reduction

[aitmessaoudali]

Hello community, i need to reduce this table using another table, but in the same time i need to keep all notices of notice_type_name='Prescription'

notice:

LOAD

    joue_id as notice_joue_id,

    boamp_id as notice_boamp_id,

    notice_indexation_id,

    source as notice_source,

    notice_id,

    reference_id as notice_reference_id,

    source_id as notice_source_id,

    "type" as notice_type,

    contract_id,    if(notice_source_intitule='BOAMP_V230','BOAMP_V230',if(notice_source_intitule='JOUE_V2080201','JOUE_V2080201',if(notice_source_intitule='JOUE_V2080301','JOUE_V2080301',if(notice_source_intitule='JOUE_V2090101','JOUE_V2090101',if(notice_source_intitule='JOUE_V2090201','JOUE_V2090201',if(notice_source_intitule='PQR_IMAVIS_V2','PQR_IMAVIS_V2',if(notice_source_intitule='UPSTREAM_PROJECT_LEGACY','UPSTREAM_PROJECT_LEGACY','Prescription'))))))) as notice_source_name,

    content_type as notice_content_type,

    notice_publication,

    boamp_procedure as notice_boamp_procedure,

    contracting_type as notice_contracting_type,

    joue_procedure as notice_joue_procedure,

    date(notice_publication) as date_notice_publication,

    Year(notice_publication) as year_notice_publication,

    Month(notice_publication) as Month_notice_publication,

    Day(notice_publication) as day_notice_publication,

//     boamp_procedure as notice_boamp_procedure,

    If(type=1, 'Avis de pré-information', If(type=2, 'Avis de pré-qualification', If(type=3, 'Avis d appel d offres', If(type=4, 'Avis correctif', If(type=5, 'Avis d annulation', If(type=6, 'Avis de sous-traitance', If(type=7, 'Intention de conclure',If(type=8, 'Avis d attribution', If(type=9, 'Permis de construire', If(type=10, 'Permis de démolir', If(type=11, 'Prescription',If(type=12, 'Avant-projet', 'autres'))))))))))))as notice_type_name

FROM [lib://QSData/notice3.qvd]

(qvd);

Thanks in advance.

Ali

[YoussefBelloum]

Hi,

to reduce the table above using another table, you should have a joining key between these tables and try this:

load notice table and make right join on it

or

load the second table and make a left join on it using notice table.

and to be sure your condition to "keep all line when notice_type_name=Prescription " is met, try using a where clause on the table you will be using to filter your final table

[aitmessaoudali]

thanks for the reply, here is the table i'm using for the reduction:

LET vTableName = 'faux_plafonds3';

search3:

INNER JOIN (contract)

LOAD

contract_id

FROM [lib://QSData/$(vTableName).qvd] (qvd);

where do i put the where condition?

[YoussefBelloum]

So, I suppose the joining key between NOTICE table and the table above is: contract_id , right ?

notice:

LOAD....,

          contract_id

FROM...

right join(notice)

LOAD...,

          contract_id

FROM...

WHERE notice_type_name='Prescription';

[aitmessaoudali]

i can't write this way:

Inner Join(notice)

Load contract_id

resident search3;  

WHERE notice_type_name='Prescription'

[aitmessaoudali]

don't mind my last message, i found a way to write it correctly. the problem now is if i want to use the 'where' condition, notice_type_name needs to be in search3 because this is what i get as an error while loading data

[YoussefBelloum]

I put where notice_type_name.. and I don't really know the name of that field on your search3 table..

I don't even know if you have it. if you don't have it, try just with the join and see if it is the expected output

[aitmessaoudali]

okay, thanks appreciate the help

[YoussefBelloum]

you're welcome,

let us know

[aitmessaoudali]

so this is my final script:

notice:

LOAD

    joue_id as notice_joue_id,

    boamp_id as notice_boamp_id,

//     boamp_id as notice_boamp_id,

    notice_indexation_id,

    source as notice_source,

    notice_id,

//     error_correlation_id,

    reference_id as notice_reference_id,

    source_id as notice_source_id,

    "type" as notice_type,

    contract_id,

//     notice_source_intitule,

    if(notice_source_intitule='BOAMP_V230','BOAMP_V230',if(notice_source_intitule='JOUE_V2080201','JOUE_V2080201',if(notice_source_intitule='JOUE_V2080301','JOUE_V2080301',if(notice_source_intitule='JOUE_V2090101','JOUE_V2090101',if(notice_source_intitule='JOUE_V2090201','JOUE_V2090201',if(notice_source_intitule='PQR_IMAVIS_V2','PQR_IMAVIS_V2',if(notice_source_intitule='UPSTREAM_PROJECT_LEGACY','UPSTREAM_PROJECT_LEGACY','Prescription'))))))) as notice_source_name,

    content_type as notice_content_type,

    notice_publication,

    boamp_procedure as notice_boamp_procedure,

    contracting_type as notice_contracting_type,

    joue_procedure as notice_joue_procedure,

    date(notice_publication) as date_notice_publication,

    Year(notice_publication) as year_notice_publication,

    Month(notice_publication) as Month_notice_publication,

    Day(notice_publication) as day_notice_publication,

//     boamp_procedure as notice_boamp_procedure,

    If(type=1, 'Avis de pré-information', If(type=2, 'Avis de pré-qualification', If(type=3, 'Avis d appel d offres', If(type=4, 'Avis correctif', If(type=5, 'Avis d annulation', If(type=6, 'Avis de sous-traitance', If(type=7, 'Intention de conclure',If(type=8, 'Avis d attribution', If(type=9, 'Permis de construire', If(type=10, 'Permis de démolir', If(type=11, 'Prescription',If(type=12, 'Avant-projet', 'autres'))))))))))))as notice_type_name

FROM [lib://QSData/notice3.qvd]

(qvd);

Inner Join(notice)

Load contract_id

resident search3;  

Left Join (notice)

LOAD

    ref_notice as notice_reference_id,

    localisation,

    "surface m2",

    architecte

FROM [lib://AttachedFiles/plafond metallique.xlsx]

(ooxml, embedded labels, table is [plafond metallique]);

right join (contract)

load contract_id,

type as notice2_type

FROM [lib://QSData/notice3.qvd]

(qvd) where type='11';

i proceeded this way: i reduced the whole table and then i added notices of type='11' that were eliminated in the reduction process, thing is now i want to show properties of this newly added notices with fields from the table contract, but because of the last join i made i can only see their type and the contract_id. is there a solution to this ?