Hi,
I have two lists that come from an aggregate and a SQL Query. For the SQL Query, it will store the output to a structure.
Now my question is how do I combine these two outputs to a single excel file? As the export to excel function only allowed to assign 1 list.
Best Regards,
Harris Chew
Hi Chew,
Create A one structure with your columns and use "ListAppendALL" action.
Use this in Recordlisttoexcel and download it.
Hope this will help you
Regards
Rahul Sahu
Rahul Sahu wrote:
Hi Rahul,
I think I did not state clearly my problem statement. The two lists have different columns.
For example, List A stores 3 columns and List B Stores 2 Columns. So I want to combine these two lists together and export it to excel. So the Excel file should have 5 columns in total.
Chew Harris wrote:
Hi Chew
Find the attached demo. that will help you what i want explain.
Thank you for your clear example. Gave me a head start! I appreciate it.
Just that I need clarification as my output is kinda wrong.
This is my logic. My aggregate is retrieved in preparation. So basically I follow your example to build this logic that works well. I created a structure for my output in excel. I use ListAppendAll to append the aggregate and ListAppend2 to append the output from the SQL Query.
Below attached is the output I get. The only issue is that output is not line up as expected. Session #2 should be in line with the 1st row of People attended and absence.
Could you advise as to how to solve this issue?
Thank you
Can you do one thing, append the data of both the list at the same time in your local list and that might solve the problem.
Cheers
you can do a advance query with both entities, you do a UNION, and with the output you can export to excel.
Guys,
he doesn't want to combine rows, he wants to combine columns !
So Chew, if you can't get all the data you need from a single SQL or aggregate, you'll have to fetch some with aggregate and some with sql, and then create a local list that brings both together.
For the design of the local list, I would create a structure that exactly matches the columns that your excel should have.
You'll have to write logic to combine both result sets into the local list, and the crucial thing here is that you will have to make sure to match them up right. Do both result sets have an unique identifier in common that you can use ??
Dorine
Oh, and you are right, you did not state clearly your problem in initial post ;-)
Dorine Boudry wrote:
Hi Dorine,
thank you for your reply. Yes, I have done creating the structure and bringing the local list together. Yes, the results have both a unique identifier in common.
You can refer to my reply to Rahul just above regarding the issue I face after I have done those things.
Could you advise on that?
Thank you!
My apologies, I will keep on improving on my explanation :P
Well,
that's what I'm saying in my reply, you'll have to write logic to combine (or 'line up') records from both sources into one new list.
Can you share your oml, so we know what structures you have ?
so lets say list1 has structure ABC, and list2 has DE, than list3 must have structure ABCDE.
If for example you have one row in your excel per row in list1, you can start with a ListAppendAll into list3 from list1.
Then you'll have to loop over list2, and for each record do a ListIndexOf in list3 comparing the identifier that should match.
Also, I get a strong feeling from your questions that you are making it more difficult than it needs to be, is all the information for your excel coming from the database ? I would be surprised if it wouldn't be easier and more performant to create the right sql that extracts all the info you need at once.