Data reduction

[aitmessaoudali]

Hello community,

I have a datamodel that need to be reduced everytime we change a type of markets, the reduction script is already there, normally all i do is change the name of the folder i used in the reduction to get data of the new market.

Is there a way to automate this process, without modifying manually the names of the folders?

Thanks in Advance.

Ali

[Gysbert_Wassenaar]

What exactly do you mean when you say the model needs to be reduced? Why does it need to be reduced and how is Qlikview supposed to know that it needs to happen and what the new folder name is?

[aitmessaoudali]

Hello, this is part of the data i reduced using an inner join:

lot:

  Load

      lot_id,

    `additional_information` as lot_additional_information,

    `conditional_tranche` as lot_conditional_tranche,

    description as lot_description,

    `estimated_amount_bottom_value` as lot_estimated_amount_bottom_value,

    `estimated_amount_currency_code` as lot_estimated_amount_currency_code,

    `estimated_amount_single_value` as lot_estimated_amount_single_value,

    `estimated_amount_top_value` as lot_estimated_amount_top_value,

if(len(estimated_amount_single_value),estimated_amount_single_value,Interval([estimated_amount_top_value]+[estimated_amount_bottom_value])/2) as lot_estimated_value,

    `firm_tranche` as lot_firm_tranche,

    number as lot_number,

    object as lot_object,

    `item_order` as lot_item_order,

    response as lot_response,

    scope as lot_scope,

    state as lot_state,

    duration as lot_duration,

    contract_id,

    lot_execution_id,

    lot_execution_address,

    lot_execution_city,

    lot_execution_country_code,

    lot_execution_department_code,

    lot_execution_geo_address,

    lot_execution_geo_city,

    lot_execution_geo_country_code,

    lot_execution_geo_insee_code,

    lot_execution_latitude,

    lot_execution_longitude,

    lot_execution_geo_nuts_code,

    lot_execution_geo_zip_code,

    lot_execution_insee_code,

    lot_execution_nuts_code,

    lot_execution_zip_code,

    If(time_frame_duration_type= 'DAY',[duration]/30,If(time_frame_duration_type= 'MONTH',[duration],If(time_frame_duration_type= 'YEAR', [duration]*12,If(time_frame_duration_type= '',interval((date(time_frame_end,'DD/MM/YYY')-date(time_frame_start,'DD/MM/YYYY')),'MM'))))) as new_lot_duration,

    If(state=1, 'FUTUR', If(state=2, 'EN COURS', If(state=3, 'ATTRIBUE', If(state=4, 'NON ATTRIBUE', If(state=5, 'ECHU', If(state=6, 'SANS SUITE', If(state=7, 'INFRUCTUEUX',If(state=8, 'INFRUCTUEUX - SANS SUITE', 'autres')))))))) as lot_state_intitule

from [lib://QSData/lot3.qvd](qvd);

search_lot:

LOAD

contract_id

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

(qvd);

Inner join(lot)

load contract_id

Resident search_lot;

drop table search_lot;

this is one of tables i reduced, but i have like 10 other tables.

Now if i have another file called 'peinture' for instance instead of 'faux_plafonds3', is there a way to  modify 'faux_plafonds' by 'peinture' only once and then change happens everywhere and the reduction changes on all tables?

[Gysbert_Wassenaar]

Yes, very nice. But I'm not interested in how you do the reduction, but why it needs to be done in the first place. See the questions I posted above.

[aitmessaoudali]

i need it in order to create differenct dashboards for differenct clients who are only interested in some kind of markets.

[Gysbert_Wassenaar]

Why not use Section Access so different clients see different parts of the data?

[Gysbert_Wassenaar]

is there a way to  modify 'faux_plafonds' by 'peinture' only once and then change happens everywhere and the reduction changes on all tables?

Yes, you can use the search and replace function. Or you can use a variable.

// somewhere at the start of the script before you load the table

LET vTableName = 'faux_plafonds3'; // change it here if you want to use another table

INNER JOIN (Lot)

LOAD

     contract_id

FROM

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

     ;

[aitmessaoudali]

Many thanks Gysbert, works perfectly.