Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
tahreen371
Contributor III
Contributor III

Balance output in single row

Hello All,

the first attachement has the calculated data but now the requirment is the balance amount should be represented in single row.

like for example now you see so many Document_Number for one CUSTOMER_NUMBER, but all the Document_Number should be mapped to one and give one row of output for balance.

Say for CUSTOMER_NUMBER : 0005000043 if i select CLEARING_DOCUMENT_NUMBER: 0100071484 then all the DOCUMENT_NUMBER related to that CLEARING_DOCUMENT_NUMBER should give the balance amount in one row of whatever is remaining.


there are 2 screenshots:
1st of how the calculated data looks
2nd: expected output taken for one CUSTOMER_NUMBER

Please help ASAP

Regards.

8 Replies
tahreen371
Contributor III
Contributor III
Author

This is the original data how it looks

sunny_talwar

I guess the reason you see multiple rows is because you have dimensions which have different values on each row... may be you don't want them as dimensions... remove POSTING_..., SITE, DOCUMENT_..., DOCUMENT_NUMBER, DESCRIPTION from your chart dimension and I think the chart should just resolve to one row for each combination of CUSTOMER_NUMBER and CLEARING_DOCUMENT_NU...

tahreen371
Contributor III
Contributor III
Author

Hello Sunny,

That does not work, it still gives me multiple rows in the Balance column and also i cannot skip using those columns 

as they are in the requirement.

Please suggest

 

sunny_talwar

In this screenshot it seems that you have used Amt as a dimension... I mean your chart always shows a unique combination of all your dimension... if you want to see all these values (for example within POSTING_...)... then may be you are seeing the right output? Or which one of the following POSTING_... value would you want to see?

image.png

tahreen371
Contributor III
Contributor III
Author

Ok sunny so the important thing i got cleared again, its not neccessary to show Balance in one single row but for the Amount where balance is not pending it should show Zero value unlike in my chart it shows some value from amount.

Payment column can be skipped

So for on selection of Customer_Number it should show zero for the invoice payment is not pending and vice versa

sunny_talwar

I am not sure I follow completely... can you share a sample to check this out? Also, it would be nice to have the expected output in an Excel file for the sample your provide.
tahreen371
Contributor III
Contributor III
Author

Hello Sunny,

I have attached the script and the result that i get now and also the screenshot of expected output.

Please help on this.

And I get zero's opposite to every amount number but where u see in expected there are is no duplication of rows.

in same row it gives amount, payment details for any doc number and balance in the same row .

Regards.

sunny_talwar

1) Your expected output is for 3/17/2016, but your current output doesn't even have March of 2016... I am not sure what I need to look at.
2) What do we need the script for?