英文:
Qlik - Building a dynamic view
问题
我有一个SQL查询，用于创建一个表，每个月都会为该表添加两个与当前月份相关的新列。
我已经尝试过，但没有成功地设置在Qlik中创建一个扁平表（可视化），以便每个月自动扩展以包括这些表。是否有实现这一目标的方法？如果有，请指引我正确的方向。
英文:
I have a SQL query that creates a table, and every month 2 new columns will be added for that table related to the current month.
I have tried without success to set up a flat table (visual) in Qlik that will automatically expand every month to include these table. Is there a way to do this, and i so please point me in the right direction.
答案1
得分: 2
您可以查看CrossTable前缀。
该前缀允许将宽表转换为长表。
因此，如果我们有这样的数据：
运行以下脚本后：
CrossTable:
CrossTable(Month, Sales)
LOAD Item,
[2022-10],
[2022-11],
[2022-12],
[2023-01],
[2023-02],
[2023-03],
[2023-04]
FROM
[C:\Users\User1\Documents\SO_75447715.xlsx]
(ooxml, embedded labels, table is Sheet1);
最终数据将如下所示。您可以看到只有3列。所有xls月份列（在Item之后）现在都折叠在一个字段Month下，所有值都折叠在Sales列下。
以这种格式拥有数据允许创建“正常”图表，将Month列添加为维度并使用sum(Sales)作为表达式。
附注：如果您不想管理新添加的列，脚本可以是这样的：
CrossTable(Month, Sales)
LOAD
Item,
*
FROM
...
英文:
You can have a look at CrossTable prefix.
This prefix allows a wide table to be converted to a long table.
So if we have data like this:
After running the following script:
CrossTable:
CrossTable(Month, Sales)
LOAD Item,
[2022-10],
[2022-11],
[2022-12],
[2023-01],
[2023-02],
[2023-03],
[2023-04]
FROM
[C:\Users\User1\Documents\SO_75447715.xlsx]
(ooxml, embedded labels, table is Sheet1);
The final data will looks like below. As you can see there are only 3 columns. All xls month columns (after Item) are now collapsed under one field - Month and all the values are collapsed under Sales column.
Having the data in this format then allows creating "normal" charts with adding Month column as dimension and use sum(Sales) as an expression.
P.S. If you dont want to manage the new columns being added then the script can be:
CrossTable(Month, Sales)
LOAD
Item,
*
FROM
...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论