Export out different list to same Excel File

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

 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.

Best Regards,

Harris Chew

Chew Harris wrote:

Rahul Sahu wrote:

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

 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.

Best Regards,

Harris Chew

 

 Hi Chew

Find the attached demo. that will help you what i want explain.


Regards

Rahul Sahu

Hi,

you can do a advance query with both entities, you do a UNION, and with the output you can export to excel.

Cheers

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 ;-)


Rahul Sahu wrote:

Chew Harris wrote:

Rahul Sahu wrote:

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

 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.

Best Regards,

Harris Chew

 

 Hi Chew

Find the attached demo. that will help you what i want explain.


Regards

Rahul Sahu

 Hi Rahul,

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

Dorine Boudry wrote:

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 ;-)


 

 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

Chew Harris wrote:

Dorine Boudry wrote:

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 ;-)


 

 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.  

  • If found, update the DE part in list3 : something like list3[ListIndexOf.position].DE = list2.Current.DE
  • if not found, that depends, what do you want to do with a row that is missing ABC, do you want it in your excel or not ?


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.

 Dorine

Chew Harris wrote:

Rahul Sahu wrote:

Chew Harris wrote:

Rahul Sahu wrote:

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

 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.

Best Regards,

Harris Chew

 

 Hi Chew

Find the attached demo. that will help you what i want explain.


Regards

Rahul Sahu

 Hi Rahul,

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

 

 Hi Chew,

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