Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
cancel
Showing results for 
Search instead for 
Did you mean: 
mateo_florence
Partner
Partner

Integrate Fields

Hello,

I have a problem and to explain it I will give an example of a case.

I have loaded a Operation Table of purchase with the following fields:

BankId OperationOperation amountType of Commission 1Commission amount 1Type of Commission 2Commission amount 2
A00000199,99 €Commission A0,50 €Commission B1,50 €
B00000249,99 €Commission A0,25 €Commission C1,20 €

Each Operation can have a maximum of 2 different commissions.

I can not modify the table and I need to create the "Type of Commission" field that contains the values of the "Type of Commission 1" and "Type of Commission 2 fields". Example of what I need:

BankId OperationType of CommissionCommission amount
A000001Commission A0,50 €
B000002Commission A0,25 €
A000001Commission B1,50 €
B000002Commission C1,20 €

Which is the best solution?

PS: I do not have access to the load script

Thanks!!

3 Replies
felipedl
Partner
Partner

Hi Mateo,

I've created a script for your need, that gets each row and transforms it into two rows in a new table, containing the two possible types os comission and values.

I copied your example to a spreadsheet so i could load it into Qlik

Here's the script:

Data:

LOAD Bank,

     [Id Operation],

     [Operation amount],

     [Type of Commission 1],

     [Commission amount 1],

     [Type of Commission 2],

     [Commission amount 2]

FROM

(ooxml, embedded labels, table is Plan1);

for each BankID in FieldValueList('Bank')

NoConcatenate

_tmpTable:

Load

Bank as _tmpBank,

[Id Operation],

[Type of Commission 1],

      [Commission amount 1],

      [Type of Commission 2],

      [Commission amount 2]

    Resident Data

    where Bank='$(BankID)';

  

    for i = 0 to NoOfRows('_tmpTable')

    let j = $(i)+1;

    let _tmpIdOperation = peek('Id Operation',0,'_tmpTable');

    let _tmpTypeComission = peek('Type of Commission $(j)',0,'_tmpTable');

    let _tmpTypeAmount = peek('Commission amount $(j)',0,'_tmpTable');

 

    [Comission Amount]:

    Load

    '$(BankID)' as Bank,

    '$(_tmpIdOperation)' as [Id Operation New],

    '$(_tmpTypeComission)' as [Type of Comission New],

    '$(_tmpTypeAmount)' as [Commission Amount]

    AutoGenerate(1);

    next;

drop table _tmpTable;

next;

As a result, I get

sample.png

Attached the QVW i used.

PS: Just know I saw you cant access the script, but as to my opinion, it would be quite difficult and rather not possible to do it in a graph without data transformation.

maxgro
MVP
MVP

maybe with ValueList, look at he attachment

you can hide the yellow column in presentation tab

1.png

rubenmarin

Hi Mateo, using a bucle can be another option:

FOR i=1 to 2 // Number of comissions

Data: 

LOAD Bank, 

     [Id Operation], 

     [Operation amount], 

     [Type of Commission $(i)] as [Type of Comission], 

     [Commission amount $(i)] as [Comission Amount]

FROM  FileToLoad;

NEXT