Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have 2 tables assume table A and B in which id is the common filed, I need to compare table A id with B and need to get the non matching records count and matching records count.
Also in table B i have a status column for the id from table A i may have 2 or 3 transaction status and transaction numbers if all the status are passes then that particular should come in passed category if not it should come in failed category in the pivot or straight table created.
Let me give you a sample data
Table 1 :
ID owner
100 A
102 B
103 C
401 D
Table 2:
ID transid status
100 1 pass
100 2 pass
102 5 failed
102 8 pass
103 4 failed
Now i need to get the output as
count of passed ids = 1 (100 is the ID)
Count of failed ids = 2 (102 and 103)
count of not executed as = 1 ( 401)
Please help me in this.
Thanks in advance
Oh, a little correction
Hi Haritha. See attached
Hi,
Perhaps look at the match function or wildmatch with a where clause in your backend script
Hi Adrey,
Thanks for you quick response, it helped me
Thank you Thabang , i will go through.
I would do something like this:
Header:
LOAD * INLINE [
ID, Owner
100, A
110, B
111, A
];
Left Join (Header)
LOAD * INLINE [
ID, Status
100, pass
110, failed
];
NoConcatenate
tmp:
LOAD ID
,Owner
,If(Len(Trim(Status)) = 0, 'not executed', Status) as Status
Resident Header;
DROP Table Header;
In the front-end use Text objects:
=Count({<Status = {'pass'}>} ID)
=Count({<Status = {'failed'}>} ID)
=Count({<Status = {'not executed'}>} ID)
Oh, a little correction
Thank you Andrey,
I Have added some data , now i am expecting to get the pass count as 0 but i am getting it as 1.
. What exactly i want is for a id if any transaction got failed then that id should come under failed category and we need to take the count of it and same id should not be considered in pass count.
Thanks a lot in advance.
Please find the attachment for the same.
Yea, some more changes...
And more, Haritha, if it will be non inline load and there will be null values in transid or owner fields, the logic of matching and non matching values will not work.