Merge data from different table into one column and multiple rows - QlikSense

[Amanda_Tennakoon]

Can someone help me to achieve this?

This is the template I need to get. 

Doc Number contains -> Invoice Number and Doc Sequence Value as in order

Doc Type contains -> Invoice, Receipts, Credit Memo and Debit Memo as Types

Doc Date contains -> Invoice Date and Apply Date

Data modeling for the above-mentioned columns is below.

Black colored -> Doc Number Column

Blue Colored -> Doc Type

Red Colored -> Doc Date

Green lines are links in between these tables.

Could someone provide a way to achieve this template (1st image) using these tables?

[Amanda_Tennakoon]

Can someone help me to achieve this? @sunny_talwar @Gysbert_Wassenaar @rubenmarin 

[rubenmarin]

Hi,this needs some time and merging data step by step.

I would start with RA_CUSTOMER_TRX, loading CUSTOMER_TRX_ID and the squared fields, left join data from AR_RECIVABLE_APPLICATIONS and left join AR_CASH_RECEIPTS.

Having that megerd you can create an auxiliar table that for each data does the calculation to obtain the balance, using a group by, and create a mapping table to assign that value.

And lastly load the final tabla , loading first the values from type=invoice, and craeting the _Balance field using applymap, then concatenate the receipts.

Load order of reow looks that there is always first sorted by date and then doc. date, if you need another sorting you can add a calulated field by concatenating tables.

[ferris9584]

Hello,

Concatenate is the right way to go

LOAD Name,

     Firstname,

     Concat (Code, ', ') as Code,

     Valid

FROM 

Group By Name, Firstname, Valid;