topic Re: Data reduction in QlikView

Data reduction

aitmessaoudali — Thu, 18 Oct 2018 11:29:06 GMT

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

Re: Data reduction

Gysbert_Wassenaar — Thu, 18 Oct 2018 11:53:55 GMT

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?

Re: Data reduction

aitmessaoudali — Thu, 18 Oct 2018 12:49:40 GMT

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?

Re: Data reduction

Gysbert_Wassenaar — Thu, 18 Oct 2018 13:26:48 GMT

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.

Re: Data reduction

aitmessaoudali — Thu, 18 Oct 2018 13:28:49 GMT

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

Re: Data reduction

Gysbert_Wassenaar — Thu, 18 Oct 2018 13:30:04 GMT

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

Re: Data reduction

Gysbert_Wassenaar — Thu, 18 Oct 2018 13:34:14 GMT

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)

;

Re: Data reduction

aitmessaoudali — Thu, 18 Oct 2018 14:15:53 GMT

Many thanks Gysbert, works perfectly.