comapre two data sets coming from two different sources

[vrunda]

I have two different data sources having same info, want to compare with each other and find out unique items in each data set.
The issue is one data set has alphanumeric & character in it & other has numbers .
I am attaching file for reference.
Thank you!

 

[GraH - Guido]

Add both tables to PQ. Merge using Full Outer Join on all columns. Expand.

 

[vrunda]

I tried it through PQ as you said but it did not matched fully --sample :: from old software these were left out but these are avialble in new software ,

9737 available in new	03AAMCS9704	97132​	92507​
10227 available in new	03AAMCS9704	390172​	371592​
CSIC/06055/18-19 only 6055 in new	06AADCA5125	22395​	21328.9​
CSIC/07277/18-19 7277 in new	06AADCA5125	34141​	32515.66​
CSIC/09464/18-19	06AADCA5125	16599​	15808.24​
CSIC/12293/18-19	06AADCA5125	21012​	20011.68​

 

[vrunda]

Thank you !
Pls see my above

Add both tables to PQ. Merge using Full Outer Join on all columns. Expand.

Pls see my above table which how to match pt no ignoring alphabets or characters , all other data matches.

Please suggest ::
how did you put text in new column saying data not in old etc.
What is importance of index column.

 

[GraH - Guido]

Hi,
How well do you already know PQ?
When loading data, PQ will guess data types. I've forced this step by setting data types myself.
Keeping all data from both tables is the result of using a Full Outer Join.
The last column is a simple if ... then ... else or conditional custom column.
The index columns allow to easy identify the records in the original data sets.
-> Go through the applied steps and click the gear icon for better understanding

I don't understand what your issue is in #3. Can you upload a file and indicate what's wrong and what's expected (manually)?

 

[vrunda]

Please see the attached sheet . I have highlighted the cells which are in both old & new .

-> Go through the applied steps and click the gear icon for better understanding

I did this , Thank you so much for explaining & help!!

 

[GraH - Guido]

So not all columns should be identical.
With PQ you can extract data before, after or between delimiters.
Link is made on PT NO and Cust ID.

 

[vrunda]

Thank you so much, you saved my so much time. This forum is really great with great people here.

I shall do this with my large data set. and hopefully it will work.
Thank you again!!

 

[vrunda]

I tried with large data set & i found there are delimiters at different position . I am attaching the original data
Please see how we can compare these two data.
Thank you!!