Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have few fields like below:
[Project Name] [UT1] [UT2] [UT3] [UT4]
XXX Y N [NULL] [NULL]
YYY N [NULL] [NULL] [NULL]
[UT] fields will can only have 3 values: Y, N, [NULL]
Now, I need to create a consolidated report based on below logic:
Project Name | Unit Testing Report |
XXX | Amber |
YYY | Red |
XXX is Amber since 1 Y out of 2 entries available (ignoring Null). So 50%
YYY is Red since 0 Y out of 1 entries available (ignoring Null). So 0%
Can you please help me in creating this above straight chart.
Thanks
Hi :
Here you have a esplendid introduction into the NULL world, made by HIC NULL handling in QlikView
Hope this helps,
Joaquín
Try this:
Table:
LOAD *,
If(RangeAvg(UT1, UT2, UT3, UT4) = 1, 'Green', If(RangeAvg(UT1, UT2, UT3, UT4) >= 0.40, 'Amber', 'Red')) as [Unit Testing Report];
Load [Project Name],
Dual(UT1, If(UT1 = 'Y', 1, If(UT1 = 'N', 0))) as UT1,
Dual(UT2, If(UT2 = 'Y', 1, If(UT2 = 'N', 0))) as UT2,
Dual(UT3, If(UT3 = 'Y', 1, If(UT3 = 'N', 0))) as UT3,
Dual(UT4, If(UT4 = 'Y', 1, If(UT4 = 'N', 0))) as UT4;
LOAD * INLINE [
Project Name, UT1, UT2, UT3, UT4
XXX, Y, N
YYY, N
];
Output:
Hi sunindia,
Can we please have this in chart exp and not while loading.
Hi,
see attachment.
Regards,
Antonio
Try this expression with Project Name as dimension
=If(RangeAvg(If(UT1 = 'Y', 1, If(UT1 = 'N', 0)),
If(UT2 = 'Y', 1, If(UT2 = 'N', 0)),
If(UT3 = 'Y', 1, If(UT3 = 'N', 0)),
If(UT4 = 'Y', 1, If(UT4 = 'N', 0))) = 1, 'Green',
If(RangeAvg(If(UT1 = 'Y', 1, If(UT1 = 'N', 0)),
If(UT2 = 'Y', 1, If(UT2 = 'N', 0)),
If(UT3 = 'Y', 1, If(UT3 = 'N', 0)),
If(UT4 = 'Y', 1, If(UT4 = 'N', 0))) >= 0.40, 'Amber', 'Red'))