Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
aitmessaoudali
Creator
Creator

Data reduction

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

9 Replies
YoussefBelloum
Champion
Champion

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
Creator
Creator
Author

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
Champion
Champion

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
Creator
Creator
Author

i can't write this way:

Inner Join(notice)

Load contract_id

resident search3;  

WHERE notice_type_name='Prescription'

aitmessaoudali
Creator
Creator
Author

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
Champion
Champion

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
Creator
Creator
Author

okay, thanks appreciate the help

YoussefBelloum
Champion
Champion

you're welcome,

let us know

aitmessaoudali
Creator
Creator
Author

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 ?