Missing data from the input tool

[adelsd]

Hello,

I'm writing to you because I'm experiencing an issue with loading my data, which is causing me to lose several rows in my dataset.

I have a dataset with 273,969 rows and 37 columns, including one named CONT_ID. When I try to find the value 15580 in the CONT_ID column by clicking on the three dots next to the column name, I don't get any results. However, when I apply the filter by clicking on the green arrow to create a filter tool, or when I manually create the filter tool, I do find the row.

Initially, I thought it was just a display issue because I have the correct number of rows. However, when I join this dataset with another using the CONT_ID column, values like 15580 that I can't find directly are not included in the join, and I end up with fewer rows than expected.

If anyone has encountered this problem before or can see the mistake I'm making, could you please provide a solution?

[apathetichell]

This is expected and totally normal:

https://help.alteryx.com/current/en/designer/workflows/results-window.html#idp320260

By default, the Results window displays the number of records shown in the window out of the total number of records in the data. For most tools, the Results window displays up to 1 MB of data, or up to 1,000 bytes of string data, for each tool anchor. If data exceeds 1 MB, the Results window displays a message that states that partial results are displayed.

• To view all data from a tool in the Results window, add a Browse tool, and rerun the workflow.

• To view more than 1 MB of data in the Results window without a Browse tool, adjust Memory Limit per Anchor in User Settings.

[OTrieger]

@adelsd 

Have you checked if this id is in the L or R output as in the J you will only have the matched results.

[apathetichell]

re the join -> use a browse/filter tool. if it's not showing up - there is a mismatch in your data. You can test this by attaching filter tools before your join -> filtering on what you are joining on -> and testing what the values look like.

[adelsd]

The id is in the L output but it should be in the J. I think the issue is coming from the right dataset where I can't see the value 15580 using the quick filters but which I see using the filter tool. 

[OTrieger]

@adelsd 
If the data does not join it means that it is not 100% the same, there is difference, can be even a space. When I know that the item is in both data sets, what I do I find the items with Filter tool and then copy and paste the results to an excel file, the result from the L and the result from the R and then using = function. You will get False. Click on each of the cells in excel and find the difference.

[adelsd]

The value in the left dataset was stored as a string, and even though I modified the type with a Select tool, I believe it was still stored as a string. I used a Formula tool and the ToNumber() function, and now it works. Thank you for your help! 

[OTrieger · Accepted solution]

@adelsd  Cool :)

You can use Select Tool to Force Data type. It can be that in different files that data type will differ, therefore when using Select Tool you can set the data type to Forced as Double etc. this will ensure that the data will always be Double type no matter what. I'm using it when I know that the data type for a specific field is keep changing, I do not know why but it is, and I know that it has to be Double, so in select tool I choose Double Forced.