Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
A sample of my data is below and attached. Each facility, identified using a [Facility Code] reports a [Value] for each [Pollutant]. Facilities are categorized into industries using [PSC code].
I am looking to calculate the skew of the [Value] for each [PARAMETER_DESC] within a [PSC code] and then count the number of [Pollutants] within a [PSC code] with a positive skew. Qlik has an in-built skew function, but I can't seem to perform both steps at once!
The final visualization will be TABLE that lists [PSC code] and the number of pollutants with a positive skew. If I could graph this - potentially a bar graph that shows PSC code on the x-axis and number of pollutants with a positive skew on the y axis, that would be great too!
Any help would be great! Thank you!!
PSC code | Facility Code | Pollutant | Value |
435 | AR0052051 | Chromium, trivalent total recoverable | 0.8601 |
450 | WV0004499 | Benzo[b]fluoranthene | 0.0069 |
435 | WV1015848 | Manganese, total [as Mn] | 2 |
435 | WV1005481 | Aluminum, total [as Al] | 0.43 |
434 | WV1023951 | Aluminum, total [as Al] | 0.14 |
450 | UTR000601 | Aluminum, total [as Al] | 0.9 |
435 | CTU005023 | Chromium, trivalent total recoverable | 0.4 |
420 | MDG914106 | Chromium, trivalent total recoverable | 0.786363 |
420 | LAL036412 | Benzo[b]fluoranthene | 0.654 |
Hmm, I tried to solve the problem, by using a truncated and cleaned Inline statement:
DATA: LOAD * Inline [ 'PSC code','Facility Code','Pollutant','Value' '435','AR0052051','Chromium, trivalent total recoverable','0.86010004', '434','WV1015848','Manganese, total','2', '434','WV1005481','Aluminum, total','0.43', '434','WV1023951','Aluminum, total','0.14', '435','KYR10I122','Chromium, trivalent total recoverable','0.3', '420','LAL036412','Benzo','0.654', '434','MAU000097','Chromium, trivalent total recoverable','0.235', '434','PRU201920','Manganese, total','0.985', '434','UTR000146','Aluminum, total','0.21', '450','UTR000601','Aluminum, total','0.9', '435','CTU005023','Chromium, trivalent total recoverable','0.4', '420','CTU005024','Benzofluoranthene','0.2', '450','UTR000668','Chromium, trivalent total recoverable','0.9', ] ; EXIT Script ;
But the documentation on Skew is kind of weird, they don't even use any of the higher forms involving Set Analysis. I thought AGGR would have to be thrown in the mix, "Aggr(Skew( TOTAL <[PSC code]> Value),[PSC code], [Pollutant])" but all that did was throw out a garbage value.
No, I opened it up in Excel, and then used this to create the rows for the script:
=CONCATENATE("'",A1,"','",B1,"','",C1,"','",D1,"',")
Then you just copy the values into an Inline load and voila. Sorry I couldn't figure out the solution to your problem.
See the attached.
Ended up with the following measure:
=Count( aggr( if(skew(Value) > 0, skew(Value), null()), [PSC code],Pollutant ) )