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

Hello, I want the Qlik pivot table to show the maximum date per code from the total list of codes.

I'm currently using the following formula:

AVG({<[EG]={'CLOSED'}, [FEC CLI]={"$(=max([FEC CLI]))"}>} RT)

But this only gives me the overall maximum date across all codes. What I actually need is to display the maximum date for each code, since some codes have more than one date.

I attached an Excel file for better understanding.
I hope you can help me. Thanks!!!!