CrossTab tool giving empty rows ( Need some help please !!)

[AbhirupMukherjee09]

Hi Everyone,

I am looking for a solution to my current problem.

My O/P from the cross-tab tool is coming as

  Name Email     DAY1          DAY2

   A          aa      Present    

   B         bb      Present        

   A          aa                           Present

   B         bb                            Absent.

Used Name and Email in the group by section of Cross Tab, Attendance in the values part and Day column in the header part in the Cross Tab)

Now, I ideally I want to remove those empty values and limit the output to 2 rows only, where the DAY1 & DAY2 values are on the same side. 

I was thinking of using of using a Filter Tool, any have any solution to this type of problem. Please Help 

[Felipe_Ribeir0]

Hi @AbhirupMukherjee09 

Check if it is that that you need, workflow attached:

[Emmanuel_G]

@AbhirupMukherjee09 

Many possibilities are possible to do that.

You can use just summarize tool as shown below by selecting the longest values ( so it's sure null is not ), the first, etc. 

Find in attachement an example with your data.

Let me know if this solution is ok for you.

[AbhirupMukherjee09]

Hi there,

The main issue is that I have created a macro to calculate the Day wise attendance (Present or Absent) with Day1 source file as template so I am not sure how to configure the Summarize tool in the macro for Day2 as the macro when connected to the input source files can have Day1, Day2 multiple days. 

I hope, I am able to explain my problem. Any help on this ?

[AbhirupMukherjee09]

Hi ,

The main issue is that I have created a macro to calculate the Day wise attendance (Present or Absent) with Day1 source file as template so I am not sure how to configure the Summarize tool in the macro for Day2 as the macro when connected to the input source files can have Day1, Day2  etc. 

I hope, I am able to explain my problem. Any help on this ?

[AbhirupMukherjee09]

The data before Cross Tab tool in the macro looks something like - 

Day     Name  Email  Attendance

Day1    A          aa     Present      

Day1    B         bb      Absent

There will be data from Day2 input files and the output comes as the above posted in the question with empty rows.

[Felipe_Ribeir0]

Sorry, i could not understand what is the input and what is the expected output.

[AbhirupMukherjee09]

Hi the expected output format is the one which I have posted on the question body. (Name Email Day1 Day2..Day N ) columns, I have actually created a macro as mentioned above for calculating day wise attendance taking only the Day1 source file which has the information for only Day 1, This macro will be then connected to multiple input files from Day1..DayN. The output result which I am getting after running the app is like - Name Email Day1 Day2 

            A.         aa.    P.        

             A.         aa.              A.

I am pretty sure if I load 3 files (day1 , day2, day3) then the output will be like - Name Email Day1 Day2 Day3 

                                            A.        aa.     P

                                             A.         aa.             A

                                             A.        aa.                        P

I want only one row for the above. Does that make sense ? 

[AbhirupMukherjee09 · Accepted solution]

I have finally found the solution, the Cross Tab  connected with the  Batch Macro (I was using the Cross Tab inside the macro)