Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
Thank you for looking into this question.
I want to use the Repair ID in Table2 to compare with Table1 to count for how many entry is match there. In this case, the result would be 2. May I know any functions can achieve this? Thanks
Data Table1 from OBDC:
RepairID CarPlateID
1001 MAZ1
1002 MAZ2
1003 MAZ3
1004 MAZ4
1005 MAZ5
1006 MAZ6
1007 MAZ7
Data Table2 from OBDC:
RepairID
1006
1007
1008
1009
1010
1011
Use Lookup function
Hello,
Try something like this:
Table1:
Load * Inline
[RepairID, CarPlateID
1001, MAZ1
1002, MAZ2
1003, MAZ3
1004, MAZ4
1005, MAZ5
1006, MAZ6
1007, MAZ7
];
Table2:
Load * Inline
[
RepairID
1006
1007
1008
1009
1010
1011
];
Common:
Load RepairID As CommonIDs
Resident Table2;
Inner Join
Load RepairID As CommonIDs
Resident Table1;
After that, you can use this to get the number of common ID's between tables:
=Count(CommonIDs)
Hope this helps!
May be this?
Table1:
LOAD * INLINE [
RepairID, CarPlateID
1001, MAZ1
1002, MAZ2
1003, MAZ3
1004, MAZ4
1005, MAZ5
1006, MAZ6
1007, MAZ7
1008, MAZ8
];
LEFT JOIN(Table1)
Table2:
LOAD *, RowNo() AS RowCnt INLINE [
RepairID
1006
1007
1008
1009
1010
1011
];
Use a count(RowCnt) in your textobject or any chart as an expression.
Or just use
1 AS RowCnt in table2 replacing RowNo().
Thank you all for looking into this! May I know is it possible to perform calculation in Expression level rather than in the script?
Hi,
Check this.
Table1:
Load RepairID,CarPlateID,RepairID as ID Inline
[RepairID,CarPlateID
1001,MAZ1
1002,MAZ2
1003,MAZ3
1004,MAZ4
1005,MAZ5
1006,MAZ6
1007,MAZ7
];
Table2:
Load * Inline
[RepairID
1006
1007
1008
1009
1010
1011
];
Inner join (Table1)
Test1:
Load RepairID as Common_RepaireID Resident Table2 where Exists(ID,RepairID);