Card FAQ
1. When fields are dragged into the color bar, the legend doesn't distinguish by numeric fields anymore
For legend colors, "Color" has higher priority than "Numeric".
2. Solution for direct connection database integer division results still being integers
Multiple databases like Greenplum, PostgreSQL, Presto will have the situation where dividing 2 integers still results in an integer, losing decimal precision.
[Solution]
-
Solution 1: Multiply the numeric value by 1.0. Multiplying either the numerator or denominator by 1.0 can convert integers to floating point numbers, but note that the number of decimal places may affect data precision. The number of decimal places should be determined as needed. Guandata BI supports up to 6 decimal places, so even multiplying by 1.00000000000 will only retain the first 6 digits.
-
Solution 2: First convert either the numerator or denominator field to double/float type, then use the round() function, or cast(xxxxx as decimal()) to specify the number of decimal places to retain. Please use the corresponding database functions.
? Solution 2 Example
3. Solution for long numeric display errors
[Background]: When users create file datasets using CSV files, or create new extraction datasets, there are fields storing long numeric values over 15 digits (such as order IDs, user IDs). After creating the dataset, the system identifies them as Long type fields, then the values change, for example, the last few digits may display as 0. In this case, when creating reports later, they will always use incorrect data.
[Cause]: Long type and Int type are similar, used to store integers. Although Long type is generally considered to occupy 4 bytes with a maximum value of 10 digits in the numeric range (approximately 2.1 billion), it may not be uniform under different compilation environments. This situation also frequently occurs when opening CSV files with Excel, or directly entering long numeric values in Excel cells.
[Solution]
-
Solution 1: Upgrade BI version to 4.4 and above.
-
Solution 2: If you don't want this type of field value to change under any circumstances, it's recommended to store long numeric formats as String strings in the original database, or change the field type to String during the final preview stage of dataset creation, or convert the field type to String string later (Spark function recommended cast([field_name] as string)).
4. Incorrect results when comparing timestamps and dates
[Cause]: The date format is timestamp format, but ">=2020-02-02" or "<=2020-02-02" has "2020-02-02" in string format, which is compared as strings, meaning comparing length, so there will be problems.
[Solution]
-
Solution 1: Change the date format in dimensions: Use the to_date() function to change timestamp format to date format.
-
Solution 2: Change the comparison condition. Directly change the comparison condition to: [date]>= "2020-02-02 00:00:00" and [date] <= "2020-02-02 00:00:00".
? Derived Problem
[Problem] Two parameter filters respectively set start time and end time, and default to {{{yesterday}}}. When default values or selecting the same day, linked cards will have no data. [Cause] Investigation found that the dataset's date field is in timestamp format, so it cannot be compared correctly. The solution can refer to Solution 1, using the to_date() function to change timestamp format to date format. [Summary] When doing date comparisons, the format or string length should correspond. If it's timestamp format, then the comparison condition should also be timestamp format in principle. If it's date format, then the comparison condition should also be date format in principle.
5. When doing distinct count, are empty strings also counted?
Yes, empty strings are counted. If you don't want to count them, you can replace empty strings with null values, and null values are not counted.
6. When using cards to create detail data downloads, why are some limited to 50,000 rows and some limited to 100 rows?
The default limit for exporting data as Excel is 50,000 rows, which can be modified from administrator settings; but if the display quantity is set to 100 in the card editing page dimensions, then the exported data will be 100 rows.
7. If text format fields are placed on numeric values, what are the display logics when aggregation methods are "No Processing", "Maximum", and "Minimum"?
Text can also be compared by size, following ASCII code order. Maximum is max(), minimum is min(), no processing takes the first row in the dataset (no sorting).
8. Why are two cards with the same direct connection dataset and same filtering conditions inconsistent?
[Possible Cause]: Caused by direct connection dataset caching.
If the dataset enables "Support real-time card data" and the page enables "Real-time data", then card data may be cached (specific cache validity period depends on the settings in dataset/data updates).
Card editing will trigger data queries. In the card editing page, if "Auto-refresh data after changes" is enabled, each edit will refresh the cache and fetch data directly from the database; if "Auto-refresh data after changes" is not enabled, then each edit is within the cache validity period, querying cached data.
[Solution]
-
After saving card edits: Refresh the page uniformly (purpose: refresh cached data).
-
In direct connection dataset/data updates, when setting cache validity period, you can choose no cache (purpose: eliminate cached data as much as possible).
Others: View more about direct connection dataset caching
9. When editing cards, it prompts "Dataset row count supporting auto-refresh has reached the limit"?
[Cause]: This situation occurs because the dataset is too large (over 1 million rows). To ensure system operation efficiency, this switch is not allowed to be turned on by default. You can modify the limit of dataset row count supporting auto-refresh when editing non-direct connection cards in Administrator Settings-Operation Management-Card Parameter Configuration.