topic Re: Balance output in single row in QlikView

Balance output in single row

tahreen371 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Balance output in single row

tahreen371 — Mon, 17 Dec 2018 12:42:18 GMT

This is the original data how it looks

Re: Balance output in single row

sunny_talwar — Mon, 17 Dec 2018 12:44:26 GMT

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

Re: Balance output in single row

tahreen371 — Mon, 17 Dec 2018 12:51:53 GMT

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

Re: Balance output in single row

sunny_talwar — Mon, 17 Dec 2018 12:54:52 GMT

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?

Re: Balance output in single row

tahreen371 — Mon, 17 Dec 2018 13:06:01 GMT

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

Re: Balance output in single row

sunny_talwar — Mon, 17 Dec 2018 13:10:17 GMT

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.

Re: Balance output in single row

tahreen371 — Tue, 18 Dec 2018 04:06:41 GMT

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.

Re: Balance output in single row

sunny_talwar — Tue, 18 Dec 2018 11:59:43 GMT

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?