how to to compare of two tcolumns

[#1]

i have two columns  i want to calculate

I want to know how many id1 & Id2 are blank

I want to know how many id1values &id2(blank values)

I want to know how many id1 & Id2 are same

I want to know how many id1(blank values) and id2(values)

for this scenrio i used    count(id1=id2 ,dual(0,1)) , and created flags for id1 and id2 tried so many functions

i attached some sample data ....PFA

[sunny_talwar]

Is this what you want?

Try the script:

Table:

LOAD *,

  If(Len(Trim(id1)) = 0, 1, 0) as id1NullFlag,

  If(Len(Trim(id2)) = 0, 1, 0) as id2NullFlag,

  If(Len(Trim(id1)) = 0 and Len(Trim(id2)) <> 0, 1, 0) as id1NullId2NotNullFlag,

  If(Len(Trim(id2)) = 0 and Len(Trim(id1)) <> 0, 1, 0) as id2NullId1NotNullFlag,

  If(id1 = id2, 1, 0) as DupplicateFlag;

LOAD id1,

    prod,

    CUSTID,

    id2,

    prod1,

    CUSTID1

FROM

Community_172382.xlsx

(ooxml, embedded labels, table is Sheet2);

[maxgro]

in a dimensionless chart (just 2 of your question)

sum(if(IsNull(id1) and IsNull(id2),1))

sum(if(id1=id2,1))

[Qrishna]

See attached for the counts what you asked for.

[#5]

i did as you given the code but the output is not matching ....

[sunny_talwar]

It is not matching to what? which flags do you think are not working? Will need to know more details?

[sasiparupudi1 · Accepted solution]

a:

LOAD id1,

    prod,

    CUSTID,

    id2,

    prod1,

    CUSTID1

FROM

(ooxml, embedded labels, table is Sheet1)

Where RowNo()<11;

NoConcatenate

LOAD *,

if((len(trim(id1))=0 and len(trim(id2))=0),1,0) as Id1NullandId2Null,

if((len(trim(id1))>0 and len(trim(id2))=0),1,0) as Id1NotNullbutId2ValNull,

if((len(trim(id1))=0 and len(trim(id2))>0),1,0) as Id1ValNullbutId2NotNull,

if((trim(id1)=trim(id2)),1,0) as Id1EqualtoId2

Resident a;

drop    Table a;

[#8]

Hi Sasidhar,

i got exact output can you please tell in the above requirement  how to get (id1notmatchedid2) values excluding nulls ..

Regards

Shyam

[#9]

Hi Sasidhar,

I have done exactly what the code you gave and but i need another help if i am loading all dates the output is mismatching could you help me  on this

Regards

Shyam

[#10]

Hi Sunny

i have done exactly what the code you gave and but i need another help if i am loading all dates the output is mismatching could you help me  on this

Regards

Shyam