Hi All,
I have a query generated output in the below mentioned format.
Col1 Col2 Col3 Col4
A B C D
A B C D1
A B C D2
A B C D3
Now, my requirement is to Concatenate D and D1, D2 and D3 in Cognos. I tried various Concat options. But could not get the desired output. Kindly advise me on this. Thanks in advance!
Quote from: Praveena on 04 Jul 2016 04:57:28 AM
Hi All,
I have a query generated output in the below mentioned format.
Col1 Col2 Col3 Col4
A B C D
A B C D1
A B C D2
A B C D3
Now, my requirement is to Concatenate D and D1, D2 and D3 in Cognos. I tried various Concat options. But could not get the desired output. Kindly advise me on this. Thanks in advance!
Can you explain what you mean by concatenating and perhaps provide a sample layout of what you want? Have you looked into using a crosstab or repeater layout container? Concatenation is not a concept that works row-wise.
Hi Lynn,
Col1 Col2 Col3 Col4
20-Jan Mr. Swart Available Debit (last Month)- 5896
20-Jan Mr. Swart Available Debit (last Month)Paid- 1589630
20-Jan Mr. Swart Available Debit-8956
20-Jan Mr. Swart Available Debit Paid-1258956
Above are the details. So, I need a solution to concatenate Col4 for first two rows as a single column and last two rows together separately.
Desired Output is
20-Jan Mr. Swart Available Debit (last Month)- 5896 , Debit (last Month)Paid- 1589630
20-Jan Mr. Swart Available Debit-8956 , Debit Paid-1258956
I have an (admittedly, ugly) idea for a solution in mind. Tell me, are these four rows you see the maximum number of rows for these values of (Col1, Col2, Col3)?
Yes,
Okay, I realized during building this that I should have asked for always *EXACTLY* 4 rows per (Col1, Col2, Col3), not for maximum. Anyways, for that one, I have a solution (also see query setup in the attachment):
- In your original query, add a data item "[count]" with a definition of running-count([Col4] for [Col1],[Col2],[Col3])
- Now add 4 referencing queries (I have no clue if that is the formal term, check attachment for what I mean) referencing your original query, with filters on [count]=1, [count]=2, and so forth. pass through Col1 through Col4
- Join referencing queries 1+2, and 3+4. Inner join on Col1, Col2, Col3. In these, you do the concatenation "[concat]" on [refQuery1].[Col4] || [refQuery2].[Col4]. Also, in these, I've added a data item "[row control]" which is set to a static value of 2 in the join of 1+2, and a static value of 4 in the join of 3+4
- add 2 referencing queries referencing the joins. you do nothing in these except pass through data items Col1 through Col3, concat and row control. (/edit: i am not quite certain why these are necessary, however, if I omit these on my system, cognos throws a java null pointer exception at me)
- add a union and put in the last 2 referencing queries. Pass through all 5 data items.
If in your result, you do not need lines 1 and 3 from your original data, you're done here. If you do, then
- finally join up the union back to your original query with an outer join on Col1, Col2, Col3, and [union].[row control] = [original query].[count]