Hi Community,

I have a measure with below code. 

Count({<FinancialYearH = {"$(=getfieldselections(FinancialYearLF))"}>} HAZARD_ID)

I am forcing to get hazard count of particular FY. So achieve this, I have created a filter on  "FinancialYearLF"  field with option of always select only one. So user will get only one FY count at time. But the requirement changed now and users want to select one or multiple Financial Years at any time. Is there a work around to get this? If nothing is selected, then count is getting 0 and I don't want that. It should be fine even if all years are selected in beginning and allowing users to toggle between different financial years. 

If don't use above condition, and simply use count(HAZARD_ID), I am not getting the right number.

Is there a way to select more than one value in a filter by default? 

Hi,

I post about this topic a while ago and some tried to help me.
I'm posting again because there is an update with more context on the issue.
This is my previous post.

As in the previous post,
I need to display Actual Vs. Budget for 3 sales measures - Number of Transactions, Items Sold and Sales Amount.
It works partially as suggested in the previous post. The problem is that I'm not getting total rows.

There are two additions:
* I need to display the data broken down by month (actual vs. budget for every month) and add o total columns for the selected period. The problem again is displaying the totals.
* I need to add a row for the GeneralCost under the totals, and add an additional total row to show the Profit (TotalSalesAmount - GeneralCost).

I attached an image (with a partial example) showing the output I'm trying to achieve.

I'd appreciate your help with building the pivot table.

I need to expression in a table which group rows by max of a column.

The result need to show the latest "Description" which is filled in the biggest "stage".

Thank you Q cmmunity

Hi everyone,

I have daily target data for each store – including targets for number of transactions, number of items sold, and total sales amount. I want to compare these targets with the actual performance data. So in total, I’m dealing with 6 measures (3 targets and 3 actuals).

I’d like to present this in a pivot table (or another object) so I can clearly compare actuals vs. targets, and calculate the differences. The layout I’m aiming for looks something like shown in the attached image.

However, I’m having trouble implementing this using the standard pivot table in Qlik Sense.
I’d really appreciate any suggestions or alternative approaches for achieving this kind of comparison.

Thanks in advance!

Moin

ich habe ich komme bei diesen wahrscheinlich einfachen Problem nicht weiter. Ich habe schon google benutzt uund selbst getestet, jedoch ohne erfolg.

Ich habe eine Tabelle der unteren Form (linke Seite) Ich will für meine Berechnung (Summe, Durchschnitt, etc) nur die Werte nutze, wenn Sie zum ersten Mal mit der Seriennr auftreten. Also die rechte Tabelle ist die temporäre Tabelle, die ich haben will.

Ich habe es über den Befehl

AVG(aggr(FirstSortedvalue([Wirkungsgrad],[Seriennr]), [Seriennr]))

jedoch ohne Erfolg. Syntax ist ok, jedoch erscheint in der Ausgabe kein Ergebnis.

Kann mir jemand helfen?

hi everyone,

There are tow tables linked by key in the QVW file:

the data table and the chart showing as following:

I want to filter the dim table field [Movement_Cause_Code_Quartely_CP] not equal 1300.

however, the matrix can't calculate the result when the dim field no value matched (red rectangle cells).

I need how to modify my expression in EXP1 to let it work?

Thanks.

QlikView 

Hi, 

Please help with this requirement.=, this needs to be done in UI not at Modeling side

Year Amt 2020 100 2021 200 2022 300 2023 400 2024 500 2025 600 2026 700 2027 800

NOTE we need to represent year>current years means 2026, 2027 as 2026(next), 2027(Next) on bar chart 

Requirement - create a Bar chart with year as dim and measure as sum of Amt
Case 1 No year selected show current year -2 to Current year +2 (2023,2024,2025,2026,2027)
Case2 If current year selected show current, and next two year (2025, 2026, 2027)
Case 3 If except current year any other selected show only that year.

Hello,

Can someone point me in right direction concerning calling a PostgreSQL procedure in Talend. I am using the tpostresqlRow (which resolves to tdbRow), but no matter how I call the procedure in the SQL query field, I get the same error. 

This is the call in the Query box. The procedure has no parameter inputs.

"CALL set_status_running;"

This is the error:

"syntax error at end of input"

I'm sure it must be something simple, but so far it is eluding me and I can't find any answers in web searches.

Thank you!

We are hitting an error with a table loading. The rest of the tables are fine. Can you suggest if there is a workaround on this issue?

RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String '{"status": 0, "dataMap": {"quotes": [{"cc": "", "to": "", "mode": "", "scac": "ONEY", "price": 1093.1' is too long and would be truncated
File '54/LOAD00000001.csv.gz', line 14, character 0

Handling End of table 'SCHEMA1'.'quotation_price_searches' loading failed by subtask 1 thread 1
Failed to copy data of file D:\Schema1\cloud\54\LOAD00000001.csv to database
Failed to load SCHEMA1.TABLE1 from stage, file name:
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String '{"status": 0, "dataMap": {"quotes": [{"cc": "", "to": "", "mode": "", "scac": "ONEY", "price": 1093.1' is too long and would be truncated
File '54/LOAD00000001.csv.gz', line 14, character 0
Row 14, column ""TABLE1""["api_response":18]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "SCHEMA1"."TABLE1"("id", "pol", "pod", "pol_via", "pod_via", "etd", "eta", "movement_type", "transport_mode", "shipment_size", "shipment_type", "dangerous_goods", "incoterm", "container_size", "cbm", "weight", "weight_unit", "api_response", "created_by", "created_at", "updated_at") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 FROM '@"PROD"."LZ_LEGACYODS"."ATTREP_IS_PROD_e7e143f7_e4e7_7745_84bd_83992ec59270"/54/') force=true
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String '{"status": 0, "dataMap": {"quotes": [{"cc": "", "to": "", "mode": "", "scac": "ONEY", "price": 1093.1' is too long and would be truncated
File '54/LOAD00000001.csv.gz', line 14, character 0
Row 14, column ""TABLE1""["api_response":18]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "SCHEMA1"."TABLE1"("id", "pol", "pod", "pol_via", "pod_via", "etd", "eta", "movement_type", "transport_mode", "shipment_size", "shipment_type", "dangerous_goods", "incoterm", "container_size", "cbm", "weight", "weight_unit", "api_response", "created_by", "created_at", "updated_at") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 FROM '@"PROD"."LZ_LEGACYODS"."ATTREP_IS_PROD_e7e143f7_e4e7_7745_84bd_83992ec59270"/54/') force=true
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String '{"status": 0, "dataMap": {"quotes": [{"cc": "", "to": "", "mode": "", "scac": "ONEY", "price": 1093.1' is too long and would be truncated
File '54/LOAD00000001.csv.gz', line 14, character 0
Row 14, column ""TABLE1""["api_response":18]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "SCHEMA1"."TABLE1"("id", "pol", "pod", "pol_via", "pod_via", "etd", "eta", "movement_type", "transport_mode", "shipment_size", "shipment_type", "dangerous_goods", "incoterm", "container_size", "cbm", "weight", "weight_unit", "api_response", "created_by", "created_at", "updated_at") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 FROM '@"PROD"."LZ_LEGACYODS"."ATTREP_IS_PROD_e7e143f7_e4e7_7745_84bd_83992ec59270"/54/') force=true
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String '{"status": 0, "dataMap": {"quotes": [{"cc": "", "to": "", "mode": "", "scac": "ONEY", "price": 1093.1' is too long and would be truncated
File '54/LOAD00000001.csv.gz', line 14, character 0
Row 14, column ""TABLE1""["api_response":18]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "SCHEMA1"."TABLE1"("id", "pol", "pod", "pol_via", "pod_via", "etd", "eta", "movement_type", "transport_mode", "shipment_size", "shipment_type", "dangerous_goods", "incoterm", "container_size", "cbm", "weight", "weight_unit", "api_response", "created_by", "created_at", "updated_at") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 FROM '@"PROD"."LZ_LEGACYODS"."ATTREP_IS_PROD_e7e143f7_e4e7_7745_84bd_83992ec59270"/54/') force=true
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String '{"status": 0, "dataMap": {"quotes": [{"cc": "", "to": "", "mode": "", "scac": "ONEY", "price": 1093.1' is too long and would be truncated
File '54/LOAD00000001.csv.gz', line 14, character 0
Row 14, column ""TABLE1""["api_response":18]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "SCHEMA1"."TABLE1"("id", "pol", "pod", "pol_via", "pod_via", "etd", "eta", "movement_type", "transport_mode", "shipment_size", "shipment_type", "dangerous_goods", "incoterm", "container_size", "cbm", "weight", "weight_unit", "api_response", "created_by", "created_at", "updated_at") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 FROM '@"PROD"."LZ_LEGACYODS"."ATTREP_IS_PROD_e7e143f7_e4e7_7745_84bd_83992ec59270"/54/') force=true
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100078 Message: String '{"status": 0, "dataMap": {"quotes": [{"cc": "", "to": "", "mode": "", "scac": "ONEY", "price": 1093.1' is too long and would be truncated
File '54/LOAD00000001.csv.gz', line 14, character 0
Row 14, column ""TABLE1""["api_response":18]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.
Failed (retcode -1) to execute statement: COPY INTO "SCHEMA1"."TABLE1"("id", "pol", "pod", "pol_via", "pod_via", "etd", "eta", "movement_type", "transport_mode", "shipment_size", "shipment_type", "dangerous_goods", "incoterm", "container_size", "cbm", "weight", "weight_unit", "api_response", "created_by", "created_at", "updated_at") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 FROM '@"PROD"."LZ_LEGACYODS"."ATTREP_IS_PROD_e7e143f7_e4e7_7745_84bd_83992ec59270"/54/') force=true