Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qvraj123
Contributor II

Flag for counting IDs with multiple values in another ID filed...

Hi All,

Thanks for your time. I have this scenario where I load data from a driver table Table1, followed by a left join with the data from another trans table. Both tables are transaction tables...

DriverTable:

Load

ID,

Issue_Date,

Status,

<Other detail fields>

From TransTable1;

Each ID will have multiple rows because of the data in another columns

Left Join (DriverTable)

Load

ID,

Exception_ID

Exception_Date,

Status,

<other detail fields>

From TransTable2;

I would like to count and flag .. count the Exception_ID if there are multiple IDs ..and flag it ..

LEFT JOIN (DriverTable)
LOAD *
WHERE Issue_ID_Cntr>1;
LOAD
DATE([Issue  Date],'MM/DD/YYYY') AS [Issue  Date],
[ Exception ID] AS [ Exception ID],
COUNT ( DISTINCT [Issue ID] ) AS Issue_ID_Cntr
RESIDENT RA_ICAPS GROUP BY DATE([Issue  Date],'MM/DD/YYYY'),[ Exception ID];

I have a link table which includes the metric definitions connecting the detail table DriverTable and some other dimensions...I want to calculate a metric Exceptions with multiple Issue IDs

instead of using sum(aggr ) in the object I want to tag the exception IDs with the count and flag -- use the flag in the chart object - I want to flag within the detail table.. but when I use the left join with the counter it is tagging the value to each row...instead I need the value tagged only once - attached sample dataset for Table1 and Table2

Flag the counter for the exception IDs if the exception ID has more than 1 Issue ID in the Issue Date, Issue ID, Exception ID combination

Capture.PNG

2 Replies
Highlighted
qvraj123
Contributor II

Re: Flag for counting IDs with multiple values in another ID filed...

hi, any suggestions or ideas

olivierrobin
Valued Contributor II

Re: Flag for counting IDs with multiple values in another ID filed...

hello,

and what about NOT using a left join. create another table with the key and the count. so you will have the value once