How to add approximate values of a column? - Co... - Qlik Community

Thiago_Muquem · ‎2021-10-08

Guys, good afternoon,

I have the attached table where column E sums the number of times the text in Column D appears in column A

The default formula is this:

=SUME($A$4:$A$231;"*Approvals - Strategic booking (Pre Booking)/strategic stock*";$B$4:$B$230)

I need to replicate this formula in a project done in Qlik Sense. It's possible?

Please help me!

Best Regards

____________________________________________________________________________________________________

Pessoal Boa tarde,

Possuo a tabela em anexo onde a coluna E soma a quantidade de vezes que o texto da Coluna D aparece na coluna A

A formula padrão é essa:

=SOMASE($A$4:$A$231;"*Approvals - Strategic booking (Pre Booking)/strategic stock*";$B$4:$B$230)

Preciso replicar essa formula em um projeto feito no Qlik Sense. É possível?

Se sim, me ajudem por favor!

Desde já Grato.

Taoufiq_Zarra · ‎2021-10-10

@Thiago_Muquem If I understand correctly you can use something like

Map1:

MAPPING LOAD Field,Field as Ccode INLINE [
    Field
    Approvals - Strategic booking (Pre Booking)/strategic stock
    Back Office delay
    Capacity
    Collection
    Collection Approval
    Commercial Information
    Configuration
    Credit
    Credit Approval
    Downpayment/payment by customer
    ECO Approval
    I: sharepoint approval
    International Approval
    Item excluded on Sofon/SAP
    Master Contract (Dealers)
    Master Data (Sold to/Ship to/ End user)
    Missing 3rd party items/12NC
    Missing advance receipt
    Missing documents (bank/financial)
    Missing signed contract
    PO Error
    Priorization
    Priorization/Capacity
    Sales Force Error (Not exist/wrong status/amount/equipments)
    Sofon - International approvals
    Sofon - Services
    Sofon approval nv1
    Sofon approval nv2
    Sofon approval nv3
    Sofon Issue
    Sofon Rejected/Resubmitted
    Sofon: DX/Indent Approver
    Sofon: Global Approver CPP
    Sofon: LatAm Approver CPP
    Sofon: Local Approver CPP
    Sofon: Project Manager
    Sofon: Service approver
    Strategic booking (Pre Booking)/strategic stock Approval
    TRM Approval
];




Table:
LOAD *,MapSubString('Map1', Field) as Ccode INLINE [
    Field, Value
    Configuration, 531
    Commercial Information, 154
    Priorization/Capacity, 78
    Configuration;#Capacity, 54
    Capacity, 51
    Sofon approval nv2, 42
    Configuration;#Commercial Information, 40
    Sofon approval nv1, 36
    Sofon Rejected/Resubmitted, 34
    Sofon approval nv3, 34
    Sofon: Global Approver CPP, 34
    Downpayment/payment by customer, 30
    Credit, 27
    Commercial Information;#Configuration, 26
    Back Office delay, 22
    Sofon - International approvals, 19
    Sofon - Services, 19
    International Approval, 17
    PO Error, 16
    Sofon: DX/Indent Approver, 13
    Configuration;#Priorization/Capacity, 11
    Priorization, 11
    Configuration;#Sofon - International approvals, 10
    Capacity;#Configuration, 10
    Configuration;#Sofon Rejected/Resubmitted, 10
    Credit Approval, 10
    Configuration;#Sofon - Services, 9
    Sofon Issue, 9
    Configuration;#Priorization, 9
    Configuration;#Sofon: Global Approver CPP, 9
    Configuration;#Sofon approval nv1, 8
    Missing signed contract, 8
    Approvals - Strategic booking (Pre Booking)/strategic stock, 7
    Back Office delay;#Capacity, 7
    Commercial Information;#Sofon - International approvals, 7
    Back Office delay;#Configuration, 6
    Sofon approval nv2;#Sofon approval nv3, 6
    Sofon Rejected/Resubmitted;#Sofon approval nv1, 6
    Master Data (Sold to/Ship to/ End user), 6
    Sofon approval nv1;#Sofon approval nv2, 6
    Item excluded on Sofon/SAP, 5
    Configuration;#Back Office delay, 5
    PO Error;#Commercial Information, 5
    Configuration;#Sofon approval nv3, 5
    Sofon: LatAm Approver CPP, 5
    Commercial Information;#Sofon Rejected/Resubmitted, 5
    Master Contract (Dealers), 5
    Sofon: Global Approver CPP;#Configuration, 4
    Sofon: Local Approver CPP, 4
    ECO Approval;#Commercial Information, 4
    Configuration;#International Approval, 4
    Sofon approval nv1;#Configuration, 4
    Configuration;#Item excluded on Sofon/SAP, 4
    Sofon: LatAm Approver CPP;#Sofon: Global Approver CPP, 4
    Configuration;#Sofon: Local Approver CPP, 4
    Missing 3rd party items/12NC, 4
    Commercial Information;#Credit, 3
    Sofon Issue;#Configuration, 3
    Sofon: Local Approver CPP;#Configuration, 3
    Sofon: Service approver, 3
    Sofon Rejected/Resubmitted;#Sofon: Global Approver CPP, 3
    Configuration;#Sofon: DX/Indent Approver, 3
    Configuration;#Sofon: Project Manager, 3
    Commercial Information;#Sofon approval nv3, 3
    Sofon approval nv3;#Sofon - International approvals, 3
    Priorization/Capacity;#Configuration, 3
    Regulatorio, 3
    PO Error;#Sofon Rejected/Resubmitted, 2
    Missing 3rd party items/12NC;#Configuration, 2
    Commercial Information;#Strategic booking (Pre Booking)/strategic stock Approval, 2
    Commercial Information;#Priorization/Capacity, 2
    Sofon: Local Approver CPP;#Sofon: LatAm Approver CPP;#Sofon: Global Approver CPP, 2
    Configuration;#Sofon: LatAm Approver CPP;#Sofon: Global Approver CPP, 2
    Sofon Rejected/Resubmitted;#Configuration, 2
    Priorization/Capacity;#Sofon approval nv1, 2
    Sofon: Global Approver CPP;#Sofon: LatAm Approver CPP, 2
    Priorization/Capacity;#Sofon Rejected/Resubmitted, 2
    Sofon: Local Approver CPP;#Commercial Information, 2
    Collection, 2
    Sofon: Project Manager, 2
    Sofon - International approvals;#Sofon - Services, 2
    Sofon Issue;#Sofon approval nv1, 2
    Collection Approval, 2
    Commercial Information;#Downpayment/payment by customer, 2
    Sofon approval nv1;#Sofon - International approvals, 2
    Configuration;#Capacity;#Sofon: Local Approver CPP, 2
    Configuration;#Sofon approval nv1;#Sofon - International approvals, 2
    Configuration;#Approvals - Strategic booking (Pre Booking)/strategic stock, 2
    Commercial Information;#Sofon Issue, 2
    Missing signed contract;#Sales Force Error (Not exist/wrong status/amount/equipments), 2
    Sofon approval nv3;#Commercial Information, 2
    TRM approval, 2
    Sofon approval nv3;#Configuration, 2
    Sofon: Local Approver CPP;#Sofon: Project Manager, 2
    Commercial Information;#Configuration;#Sofon Rejected/Resubmitted, 2
    Sofon: Project Manager;#Sofon: DX/Indent Approver, 2
    Sofon approval nv3;#Sofon approval nv2, 2
    Configuration;#Sofon approval nv2, 2
    Strategic booking (Pre Booking)/strategic stock Approval, 2
    Sofon - Services;#Sofon approval nv3, 1
    Sofon approval nv3;#Sofon Rejected/Resubmitted;#Configuration, 1
    Sofon approval nv2;#Master Data (Sold to/Ship to/ End user), 1
    Credit;#Configuration, 1
    Configuration;#Sofon Rejected/Resubmitted;#Sofon: Global Approver CPP, 1
    Credit;#Downpayment/payment by customer, 1
    Configuration;#Sofon - Services;#Priorization/Capacity, 1
    Credit;#PO Error, 1
    Configuration;#Capacity;#Sofon Rejected/Resubmitted;#Commercial Information, 1
    Credit;#Sofon approval nv2, 1
    Sofon Rejected/Resubmitted;#Configuration;#Sofon approval nv1, 1
    Credit;#Sofon approval nv3;#Commercial Information, 1
    Sofon: Local Approver CPP;#Sofon: Global Approver CPP, 1
    Commercial Information;#Configuration;#Capacity, 1
    Configuration;#Sofon - Services;#Capacity, 1
    Downpayment/payment by customer;#Commercial Information, 1
    Sofon approval nv1;#Sofon approval nv3;#Sofon approval nv2, 1
    Downpayment/payment by customer;#Sofon: DX/Indent Approver, 1
    Capacity;#Back Office delay, 1
    Configuration;#Commercial Information;#Priorization, 1
    Configuration;#Back Office delay;#Capacity, 1
    Configuration;#Commercial Information;#Sofon Rejected/Resubmitted, 1
    Commercial Information;#Sofon: Project Manager, 1
    International Approval;#Configuration, 1
    Sofon Rejected/Resubmitted;#Sofon approval nv3;#Sofon approval nv2;#Configuration, 1
    International Approval;#Downpayment/payment by customer;#Missing signed contract, 1
    Sofon: LatAm Approver CPP;#Sofon: Global Approver CPP;#Sofon: Local Approver CPP, 1
    Commercial Information;#Configuration;#Sofon: Project Manager, 1
    Commercial Information;#Configuration;#Back Office delay, 1
    Configuration;#Commercial Information;#Strategic booking (Pre Booking)/strategic stock Approval, 1
    Commercial Information;#Sofon approval nv2;#Sofon approval nv3, 1
    Configuration;#Credit, 1
    Sofon approval nv1;#Missing 3rd party items/12NC;#Configuration, 1
    Master Data (Sold to/Ship to/ End user);#Back Office delay, 1
    Sofon approval nv1;#Sofon approval nv2;#Sofon approval nv3, 1
    Master Data (Sold to/Ship to/ End user);#Commercial Information, 1
    Sofon approval nv1;#Sofon Rejected/Resubmitted, 1
    Master Data (Sold to/Ship to/ End user);#Sofon - International approvals, 1
    Sofon approval nv2;#Sofon - International approvals, 1
    Configuration;#ECO Approval;#Sofon approval nv1, 1
    Sofon approval nv2;#Sofon approval nv3;#Configuration, 1
    Missing 3rd party items/12NC;#Back Office delay;#Commercial Information, 1
    Sofon approval nv3;#Configuration;#Capacity, 1
    Missing 3rd party items/12NC;#Commercial Information, 1
    Sofon approval nv3;#Sofon approval nv2;#Sofon - Services, 1
    Capacity;#Master Data (Sold to/Ship to/ End user), 1
    Configuration;#Sofon approval nv2;#Sofon approval nv3, 1
    Missing documents (bank/financial), 1
    Configuration;#Sofon Issue, 1
    Missing documents (bank/financial);#Commercial Information, 1
    Sofon Rejected/Resubmitted;#Sofon approval nv1;#Sofon approval nv2;#Sofon approval nv3, 1
    Missing documents (bank/financial);#Missing signed contract, 1
    Configuration;#Sofon Rejected/Resubmitted;#Item excluded on Sofon/SAP, 1
    Commercial Information;#Master Data (Sold to/Ship to/ End user), 1
    Configuration;#Sofon: DX/Indent Approver;#Sofon: Project Manager, 1
    Missing signed contract;#Back Office delay, 1
    Configuration;#Commercial Information;#Back Office delay, 1
    Missing signed contract;#Commercial Information, 1
    Configuration;#Sofon: Project Manager;#Sofon: DX/Indent Approver, 1
    Missing signed contract;#Commercial Information;#Configuration, 1
    Credit;#Collection, 1
    Missing signed contract;#Credit, 1
    Sofon - Services;#Sofon approval nv3;#Sofon approval nv2, 1
    Missing signed contract;#Missing advance receipt;#Downpayment/payment by customer, 1
    Sofon approval nv1;#Back Office delay, 1
    Missing signed contract;#Priorization/Capacity, 1
    Sofon approval nv1;#Configuration;#Capacity, 1
    Configuration;#Capacity;#Commercial Information;#Sofon Rejected/Resubmitted, 1
    Sofon approval nv1;#Priorization/Capacity, 1
    Commercial Information;#Missing signed contract, 1
    Configuration;#Sofon - Services;#Sofon - International approvals, 1
    Commercial Information;#Priorization, 1
    Sofon approval nv1;#Sofon approval nv2;#Sofon approval nv3;#Configuration, 1
    Sofon: Service approver;#Sofon: DX/Indent Approver, 1
    Sofon approval nv1;#Sofon Issue, 1
    Configuration;#Missing signed contract, 1
    Approvals - Strategic booking (Pre Booking)/strategic stock;#Configuration;#Commercial Information;#Priorization/Capacity, 1
    Capacity;#Credit Approval, 1
    Sofon approval nv2;#Priorization/Capacity, 1
    Configuration;#PO Error, 1
    Sofon approval nv2;#Sofon approval nv1, 1
    Capacity;#Sofon: Service approver, 1
    Sofon approval nv2;#Sofon approval nv3;#Commercial Information, 1
    Approvals - Strategic booking (Pre Booking)/strategic stock;#International Approval, 1
    Commercial Information;#Sofon Rejected/Resubmitted;#Configuration, 1
    Configuration;#Capacity;#Sofon - International approvals, 1
    Configuration;#Sofon approval nv1;#Sofon - Services, 1
    Priorization/Capacity;#Sofon - International approvals, 1
    Configuration;#Sofon approval nv1;#Sofon approval nv2, 1
    Configuration;#Priorization/Capacity;#Sofon approval nv1, 1
    Sofon approval nv3;#Sofon approval nv2;#Configuration;#Commercial Information, 1
    Configuration;#Priorization;#Sofon: Global Approver CPP, 1
    Sofon approval nv3;#Sofon approval nv2;#Sofon Rejected/Resubmitted, 1
    Priorization;#Capacity, 1
    Commercial Information;#Sofon: LatAm Approver CPP;#Sofon: Global Approver CPP, 1
    Priorization;#Configuration, 1
    Commercial Information;#Approvals - Strategic booking (Pre Booking)/strategic stock;#Sofon approval nv2;#Credit, 1
    Priorization;#Sofon: Global Approver CPP, 1
    Sofon Rejected/Resubmitted;#Commercial Information, 1
    Priorization;#Sofon: Global Approver CPP;#Configuration, 1
    Sofon Rejected/Resubmitted;#Configuration;#Sofon - Services, 1
    Configuration;#Capacity;#Sofon Rejected/Resubmitted, 1
    Commercial Information;#Sofon: Project Manager;#Sofon: DX/Indent Approver, 1
    Sales Force Error (Not exist/wrong status/amount/equipments), 1
    Sofon Rejected/Resubmitted;#Sofon approval nv3, 1
    Sales Force Error (Not exist/wrong status/amount/equipments);#Configuration, 1
    Commercial Information;#Back Office delay, 1
    Commercial Information;#Sofon approval nv1, 1
    Back Office delay;#Commercial Information, 1
    Sofon - International approvals;#Capacity, 1
    Capacity;#Sofon Rejected/Resubmitted, 1
    Sofon - International approvals;#Configuration, 1
    Commercial Information;#Back Office delay;#Capacity, 1
    Sofon - International approvals;#Priorization/Capacity, 1
    Configuration;#Sofon: LatAm Approver CPP, 1
    Collection;#Commercial Information;#Credit;#Downpayment/payment by customer, 1
    Configuration;#Commercial Information;#Capacity;#Credit, 1
    Sofon - International approvals;#Sofon approval nv2, 1
    Configuration;#Commercial Information;#Master Data (Sold to/Ship to/ End user);#Sofon approval nv3;#Sofon - International approvals, 1
    Commercial Information;#Sofon approval nv1;#Sofon - International approvals, 1
    Back Office delay;#Configuration;#Sofon Rejected/Resubmitted, 1
    Sofon - Services;#Back Office delay, 1
    Credit;#Back Office delay;#Collection, 1
    Sofon - Services;#Sofon - International approvals, 1
    Sofon - Services;#Sofon approval nv1;#Back Office delay, 1
    PO Error;#Configuration, 1
    PO Error;#Missing signed contract, 1
    PO Error;#Sofon - Services, 1
];


Tmp:
noconcatenate

load  Field, Value,subfield(purgechar(Ccode,'#'),';') as Ccode resident Table;

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

How to add approximate values ​​of a column? - Como somar valores aproximados de uma coluna?

How to add approximate values of a column? - Como somar valores aproximados de uma coluna?