Reconcillation of Excel in Alteryx by Comparing and Generate an Exception Report

[rohit782192]

Hi ,

How could i generate the exception Report for the same. if there would be a macro which compare the full excel and generate the excepion report that would also work.

COL1	col2	FALSE
APPLE	apple	TRUE
LIME	LIME	TRUE
	ROSE	FALSE

Output:

EXCEPTION IN COL1 : Line 4
EXCEPTION IN Header Mismatch : Line 1

[ChrisTX]

I have tried this, without success. I treated the XLSX file as a Zip file, and tried to read all of the Excel formulas from the Sheet, then tried to re-create all of the formulas in Alteryx.  The problem is, Excel does not actually store all Formulas in the XLSX file, where you can easily read them.  There were some Excel formulas that I never found anywhere in the Excel file.  Not sure how Excel stores every single formula in an XLSX file.

If your Excel file stores only data, no formulas, you can probably read that data without problems.  But most Excel files contain multiple Formulas, which you may not be able to find stored in the file.

Chris

[rohit782192]

My requirement is simple 

[Deano478]

@rohit782192 with all due respect if the requirement is simple you should be able to figure it out for yourself 

[ChrisTX]

It sounds like you're just trying to read the data from the Excel file.  The title "Reconcillation of Excel in Alteryx" was a little confusing for me, so that's why I was talking about reading formulas from inside the XLSX file.

Try using a Formula tool: create a new field named FALSE, output data type Boolean, using the formula:  IF [COL1] = [col2] THEN 1 ELSE 0 ENDIF

A 1 will be displayed in a Boolean field as TRUE and a 0 will be displayed as FALSE.

Chris

[rohit782192]

How will you generate exception.

[apathetichell]

@Deano478 - that should be the new motto of our community.

[ChrisTX]

Sorry I can't help any more on this one.

Perhaps it simply a language barrier.  But I don't understand anything about your requirement based on "How will you generate exception".

If you can post a sample workflow with well-defined requirements, perhaps someone else can help.

[rohit782192]

RowID Column1 Column2 Column3

1

A

B

C

2

D

E

F

3

G

H

I

4

J

K

L

5

M

N

O

RowID Column1 Column2 Column3

1

A

B

X

2

D

E

F

3

Y

H

I

4

J

Z

L

5

M

N

P

RowID Column1_Compare Column2_Compare Column3_Compare Mismatch Reason

1	True (A = A)	True (B = B)	False (C != X)	Mismatch in Column3: C != X
2	True (D = D)	True (E = E)	True (F = F)	No Mismatch
3	False (G != Y)	True (H = H)	True (I = I)	Mismatch in Column1: G != Y
4	True (J = J)	False (K != Z)	True (L = L)	Mismatch in Column2: K != Z
5	True (M = M)	True (N = N)	False (O != P)	Mismatch in Column3: O != P

[apathetichell]

something like:

record id both. transpose both. use tile tool to create tile seq/nums - join on tiles create compares via formula tool filter out where there is a mismatch. use summarize tool to concat your mismatches. union back to datastream. for unmatched left (original data stream records) - create a value of no mismatches.  union.