QlikView简明教程.doc

QlikView简明教程目录1写在前面11.1测试驱动开发(TDD):11.2开发人员对业务数据的掌握22前言23QV介绍23.1QV的产生背景23.2QV的BI处理模型34第一次使用QV34.1下载与安装34.2Qvw设计介绍44.2.1后台和前台44.3QV读取数据库45内存数据模型45.1数据关联55.1.1什么是数据关联55.1.2自动内连接55.1.3表的内容模型55.1.4列的内存模型65.1.5联动显示的基础65.1.6QUALIFY加表名65.1.7UNQUALIFY65.2表格查看器66后台脚本概述66.1语句76.1.1逻辑判断76.1.2集合操作76.2变量76.3QV执行SQL语句86.4LOAD加载数据86.4.1从随后的表格中加载数据86.4.2填充特殊值86.4.3添加新的列87前台设计97.1数据的显示97.2常用控件97.2.1工作表97.2.2透视表97.2.3垂直表107.2.4表格框107.2.5多选框117.2.6垂直表118列表129透视表1210AGGR制作虚拟二维表1311快速复制图表到其他文件1312常用函数1513更多资源1513.1最好的帮助文档1513.2在线免费资源161 写在前面1.1 测试驱动开发(TDD):测试驱动开发(TDD ：Test-Driven Development)是指面对复杂的开发任务，开发人员应该从一个最小的功能点开始测试，当测试通过之后再加入新的功能点，知道开发完成。使用QlikView来挖掘数据，本质上是一个编程的过程，既然是编程就可能在刚开始的时候有BUG，这是毫无疑问的。我们不能一开始就用真实的全量数据作为开发的基础，这样除了问题非常难于排查原因。比较合理的做法是，自己创造一个测试环境，这个测试环境开始的时候只提供少量的，可以被开发者非常清楚的掌握的少量数据。直到用这个少量数据集开发完整个功能之后，再讲开发结果嫁接到真实环境。其本质上是一种测试驱动开发的过程。1.2 开发人员对业务数据的掌握QV的开发者不一定对被操作数据有着详细的了解，这时候往往需要业务人员，或者对数据比较了解的人员配合。否则可能会误用数据，从而导致结果不准确。比如一对多，和一对一的外键关系计算方式是不一样的，复杂度也不一样。如果是先没有掌握各个表之间外键的关联关系，很有可能会误用数据。2 前言本文内容根据之前对QlikView（以下简称QV）的了解和QlikView售前培训总结而成。简明教程的主要目标就是给一个没有接触过QV的开发人员、项目管理人员能够在最短的时间内学习并掌握QV最常用的功能，可以快速上手。本文的组织方式，本文从安装QV环境开始，循序渐进、由浅入深。在关键的一些函数上通常使用示例来和图像力图使读者能够容易理解。全文分文如下几个方面的内容：QV介绍、安装、常用控件、变量、表达式、脚本的结构与语法、集合筛选、Aggr。3 QV介绍3.1 QV的产生背景QV是一种BI（商业智能）软件，集成了ETL和展示的功能。在QV出现之前，传统BI的一般做法是SQL+ETL+报表工具，如图1所示：图1图1所以的开发流程主要是ETL在基础数据库的基础上按照项目需求抽取出新的维度数据方主题数据库中（也可以称作数据集市），从而报表软件直接从主题数据库中直接读取数据库形成报表。这种做法的弊端是不容易应对变更。比如维度发生变化时，要求先告知ETL生成新的主题数据，原有的主题要随之删除，否则会造成数据的冗余和管理上的不便。报表软件也需要跟进，从而和ETL一样做出同样的调整。这种做法还对主题数据库表的设计提出了更高的要求，如果前期没有很好的设计各个主题的数据库表结构，会导致做大量无用功，生成的报表价值不大。传统BI数据立方体cubes数据模型：3.2 QV的BI处理模型从传统BI的处理流程可以看出，问题主要是BI不能及时响应维度变更的需求。为此BI将数据加载到内存中使用，而不是从数据库加载。数据放到内存中使得需求变更时可以快速的读取和操作数据。4 第一次使用QV4.1 下载与安装到官方网站下载对应的版本 /download/ 注意Win8有专门的版本，其他版本不能安装到Win8上。也可以到：/cn/landing/go-sm/meet-qlikview 下载。最后你填写完信息之后进入下载页面，作出版本选择，你可能会下载这个：QlikViewDesktop_x64Setup.exe默认安装即可。也可以到下面的网盘下载：/s/1dDhNFZr安装完成之后，默认会创建桌面快捷方式，打开如下图所示：4.2 Qvw设计介绍QV处理和分析数据的一般过程是，建立qvw文件，将数据连接信息、数据清理的操作、数据模型构建、数据压缩存储、图表、变量等全部存储在qvw文件中。用户每次打开qvw文件时，上述内容会自动呈现，无需再次从数据库或者Excel中加载原始数据，可以继续上一次的编辑和分析工作。可以使用文件新建 来创建一个新的qvw文件，这是qvw文件中只有一些初始的信息，如果用户要使用qvw来完成分析还需要从加载数据开始。4.2.1 后台和前台QV包括前台和后台两部分，其中后台主要提供数据，并对数据进行必要的清洗，后台的脚本可以用编辑脚本按钮打开。后台进行数据清洗主要通过LOAD关键字添加标记位、使用条件语句、自带函数等做预处理的方式来实现。脚本中的内容一般如下：如果你的数据源发生了改变，需要重新加载数据，点击【重新加载】按钮即可重新读取数据源。QV的前台主要是设计界面，在设计界面中可以进行图表的创建，修改查询等操作。4.3 QV读取数据库如果需要了解详细的qvw从数据库表中读取数据并分析的过程，下面的这个示例完整的展示了具体的细节：/calmreason/article/details/469822075 内存数据模型为了降低内存占用，QV对内存数据结构做了压缩处理，使得数据库表中每一列重复元素只存储一次。压缩前数据在数据库表中的存储形式如下图2所示：5.1 数据关联：5.1.1 筛选一个字段就是筛选所有字段5.1.2 什么是数据关联数据关联类似于数据库中的主键、外键。如果两个表A、B，其中A的主键A.a_key是B的外键B.a_key，那么QV就会自动将A表和B表关联起来。关联之后，选择A表中的数据，B表的数据会联动被选择。表之间的联动显示，是由相同的字段名来实现的。5.1.3 自动内连接下图是【表格查看器】（后文会有介绍）自动内连接的结果图，说明QlikView会在内部数据结构中自动将字段名相同的各个表用内连接关联。这样一来，凡是被关联的字段都将作为分组字段，凡是未被关联的字段都将作为表达式字段。而且，表达式字段都默认以所在表的分组字段自动分组。5.1.4 表的内容模型表被加载到内存中之后，表中的数据被单独存储在列的内存模型中，表的行数据用引用来维持。所以在内存中数据是按列来存放的，表是按照网5.1.5 列的内存模型列的内存模型如下图所示，列到内存中之前可能会分布在多个表中，他们的名字相同，进入内存就会汇聚到同一个列中。但是关联关系由表来存储。当然，列也可以用as重命名来改变名字从而达到取消关联的目的。如果要强调某一列col来自其中某一个表A，那么可以用load关键字给A表增加一列作为标记，在想要区分的时候看看isnull(aggr(标记,col)即可。如果两个列在同一个表中被加载，那么选择一个列，另一个列就会联动被选中。如果两个列不在同一个表中被加载，那么这两列通过共同的中间列（名称相同）联动。5.1.6 联动显示的基础5.1.7 QUALIFY加表名严格区分来自每个表的列。各个字段对所有字段加上表名用来取消关联。QUALIFY *; 5.1.8 UNQUALIFY与QUALIFY的作用相反。下面的例子是对所有表中的【单项编号】字段使用自动关联。UNQUALIFY 单项编号; 注意：作用范围可以在任意位置加上表名和取消表名，可以重复使用，每次使用都会影响后续指令的执行，不会影响之前指令的执行。5.2 表格查看器如果数据已经被加载到内存，通常是多个表，这些表之间的关系通过相同的字段名称来实现。可以通过【表格查看器】按钮来查看当前加载的所有表，以及表之间的关联关系如上图所示，三个表都有“单项编号”这个字段，从而这三个表就发生了关联。6 后台脚本概述脚本是QV的后台，脚本的功能是将数据加载到内存中供用户使用。但是往往用户使用的原始数据都不一定完整、或者存在脏数据、有时候甚至需要做一些转换等等。这种加载数据的同时对数据进行清洗和预处理的工作就是QV的ETL功能。6.1 语句脚本以语句构成，多个语句用分号隔开语句是从上到下依次执行。6.1.1 逻辑判断在帮助文档中输入等于号（=）即可看到所有的逻辑操作符使用方法。6.1.2 集合操作集合用一对大括号表示： 元素用逗号隔开：1,2,3,46.2 QV执行SQL语句参考本文的“QV读取数据库”一节，可以看到QV在加载一个数据库表的时候会生成一条语句，例如：SQL SELECT 单项编号, 单项财务收入, 财务收入确认时间FROM YWW_Temp_Test.dbo.BI_单项财务收入; 关键字SQL表示后面紧接着是一个标准SQL语句。这个SQL语句一般是【选择】功能自动生成的。上面的SQL语句功能是从数据库“YWW_Temp_Test”中读取表“BI_单项财务收入”的“单项编号”、“单项财务收入”、“财务收入确认时间”三个字段。脚本执行完毕就会将上述数据存放到内存中。6.3 LOAD加载数据QV执行SQL语句可以将数据加载到内存，如果你希望加载到内存之前对数据进行一定的预处理，比如数据清洗、修饰等，就可以使用LOAD关键字来实现。Load语法是从数据库到内存最后一次加工过程，在这里你可以给表添加新的列，预处理列中的数据。LOAD语句总是和SQL语句配对执行。LOAD 语句;SQL 语句;6.3.1 从随后的表格中加载数据 Load A, B, if(C0,positive,negative) as X, weekday(D) as Y;Select A,B,C,D from Table1;6.3.2 填充特殊值例如，下面的Load就会数据库中日期为空的值做了特殊值填充处理：LOAD单项合同金额, 单项编号, If(IsNull(签回日期),makedate( 1900 ),签回日期) As 签回日期;SQL SELECT 单项合同金额, 单项编号, 签回日期FROM YWW_Temp_Test.dbo.BI_合同明细; 6.3.3 添加新的列比如你的项目类型里有各种类型，但你想对这些类型分类：设计类、非设计类。你可以想如下这样给你的表加入内存之后添加一列（这里又添加了一列：单项编号_FROM_单项）：LOAD 单项编号,1 as 单项编号_FROM_单项, 项目类型 ,if(项目类型 = 通信设计 or 项目类型 =建筑设计 or 项目类型 =装潢设计 or 项目类型 =铁塔设计 or 项目类型 =咨询 or 项目类型 =网优,设计类,非设计类) as 是否设计类;SQL SELECT 单项编号, 项目类型FROM chanzhi.dbo.BI_单项; 加载到内存的结果如下：7 变量与表达式7.1 变量脚本语言总是离不开变量，在脚本中的QV变量都是全局变量，有一些系统变量，也支持用户自定义变量。由于语句是按顺序执行，所以要求用户自己要按照一定的规范组织脚本，否则随着要分析内容的增多，会出现脚本难以管理的局面。如果用户提前预知脚本会越来越复杂，就会定期重构，使其一直处在阅读难度的合理水平。重构只要能够按照这个原则进行就可以不局限与比较死板的规范。变量的定义：定义脚本变量的语法为：set variablename = string或let variable = expression有些语句是自动生成的，比如当你新建一个qvw文档时总会出现下面的语句：SET ThousandSep=,;SET DecimalSep=.;SET MoneyThousandSep=,;SET MoneyDecimalSep=.;SET MoneyFormat=#,#0.00;-#,#0.00;SET TimeFormat=h:mm:ss;SET DateFormat=YYYY/M/D;SET TimestampFormat=YYYY/M/D h:mm:ss.fff;SET MonthNames=1月;2月;3月;4月;5月;6月;7月;8月;9月;10月;11月;12月;SET DayNames=周一;周二;周三;周四;周五;周六;周日;7.2 一般的统计求和方法8 前台设计8.1 数据的显示数据在被加载到内存中是去除重复的，并使用引用来保持表数据的组织关系。除此之外，数据并不一定是直接按照数据库中的方式存储的，QV会针对不同的数据保存为不同的类型，而需要展示的时候可以使用外观模式来显示不同的格式。例如，日期数据在被存放到内存之后可以显示为整数、日期、不同格式的日期。通常在表格的“数字”属性中会专门制定数据的显示格式，如下图所示：显示如下：如果使用默认显示，日期可能会显示成整数：8.2 常用控件8.2.1 工作表工作表是打开qvw文件时主窗口中最大的一块区域，这里用来设计各种图表。可以在同一个文件中创建多个工作表，这些工作表使用同一个数据源，在一个工作表中做出的筛选，会让其他工作表联动作出反应。8.2.2 透视表维度：一个分组，类似于数据库中的group by。对一组之后每隔组内部的多个值交给表达式中的聚合函数来处理表达式：维度会对表数据进行分组，分组之后的每一组数据，交给表达式来处理。所以，表达式通常是一些类似于sum这种聚合函数。8.2.3 垂直表 垂直表与透视表的对比透视表：1.透视表更擅长于数据的分组，在透视表中你可以很轻易的看到指定的行从属于哪个分组当中，并且每个分组都可以进行部分求和。2.透视表可以实现和交叉表一样的展现方式，比如一个或者多个平行的维度。3.透视表在排序的实现方式上，只能按照维度进行分组，先按照第一个维度进行排序然后按照第二个维度进行排序等等，没有办法按照任意列进行排序。垂直表：垂直表在排序方面比透视表强很多，你可以按照任意列进行排序，但是垂直表不擅长数据的分组，并且也没有像透视表那样的可以进行部分求和。注：建议使用透视表。8.2.4 表格框表格框用来展示数据表中的原始数据，只需要将数据表中的列添加到表格框的属性中即可。创建表格框的方法如下：在工作表的空白处右键新工作表对象表格框此时可以在【标题】栏输入表格的名称，可用字段列出了你可以使用的列，选中并添加多个列，即可自动去除重复：当仅仅使用一列数据来构建表格框的时候，此列数据会自动去除重复。8.2.5 多选框多选框可以理解为是一个下拉框，可以将一个列的所有值分组，每组取一个作为一个选项。有时候我们需要用if表达式的结果来返回两种情况，每种情况可以作为多选框的选择项。8.2.6 垂直表QlikView的内存结构：不同于数据库的表结构，QV会自动将字段名相同的所有字段统一存储，并去除重复。但是数据库表关系仍然存在。所以，QV中选择一列其实是选择了所有表的这一列（列作为维度与表无关）。9 列表例如一个单一的表达式也会自动根据表达式字段所在表的分组字段进行分组：表达式if(sum(单项财务收入),有收入,无收入) A.单项编号=C.单项编号自动与A表内连接10 透视表透视表就是以维度为分组条件，表达式展示汇总结果的二维表。维度表达式1表达式2表达式表达式单项编号Sum(单项合同金额)Sum(单项财务收入)if(sum(单项财务收入),有收入,无收入) if(sum(单项合同金额),有合同,无合同) A.单项编号A.单项编号=B.单项编号A.单项编号=C.单项编号A.单项编号=C.单项编