Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Amanda_Tennakoon
Contributor II
Contributor II

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

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

tempsnip.png

Data modeling for the above-mentioned columns is below.

tempsnip2.png

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?

Labels (2)
3 Replies
Amanda_Tennakoon
Contributor II
Contributor II
Author

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

rubenmarin1

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
Contributor
Contributor

Hello,

Concatenate is the right way to go

 

LOAD Name,

     Firstname,

     Concat (Code, ', ') as Code,

     Valid

FROM 

Group By Name, Firstname, Valid;