数据从csv到Qlik可视化

淡淡的烟草味﹌ 2021-11-14 12:28 307阅读 0赞

### 文章目录 ###

*  项目简介
 *  实施方案
 *  实施过程
 *   *  pandas对csv文件进行读取并写入到sql
     *   *  注意事项
     *  生成新的字段
     *  对每一个KPI进行计算
     *  对所有的KPI进行一个整合
 *  Qlik端的导入操作
 *   *  Qlik数据加载编辑器
 *  效果展示

# 项目简介 #

数百万的数据进行各种KPI的计算  
最后用Qlik对KPI进行可视化操作

# 实施方案 #

csv-----------pandas---------------->sql server-------------------sql------------------------>KPI---------------Qlik---------------------->可视化

# 实施过程 #

## pandas对csv文件进行读取并写入到sql ##

import pandas as pd
    import pymssql,re
    import numpy as np
    import matplotlib.pyplot as plt
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    import os
    
    
    # 指定数据库
    DB_CONNECT_STRING = 'mssql+pymssql://sa:mima@ipyin:port/Repair_KPI_New?charset=utf8'
    engine = create_engine(DB_CONNECT_STRING, echo=False)
    DB_Session = sessionmaker(bind=engine)
    session = DB_Session()
    
    # 指定csv文件地址
    file_list=os.listdir('//10.103.66.10/TeamWork-SBOMECSQ/20190730data')
    
    # csv文件的字段 一定要匹配
    cols=['ID','GEO','Vendor','Customer','Pick_up_PN','p_Model',
    'p_Product_Type','p_Commodity','p_Product_sub_type','p_PN_Desc',
    'p_Manufacturer','Pick_up_SN','Pick_up_SN_Valid','Pick_up_date',
    'RMA_Request_Date','RMA_Issue_Date','RMA_No','SOID_No',
    'Original_PN','o_Model','o_Product_Type','o_Commodity',
    'o_Product_sub_type','o_PN_Desc','o_Manufacturer','Original_SN',
    'Production_Date','Warranty','IW_Approve_Status',
    'Tracking_from_SP_to_Repair','Receiving_Date','RMA_Receiving_Date',
    'Result','Return_Type','Ship_PN','s_Model','s_Product_Type',
    's_Commodity','s_Product_sub_type','s_PN_Desc','s_Manufacturer',
    'Ship_SN','Shipping_Date','Pre_Alert_Number',
    'Shipping_tracking_Number','Arrive_to_HUB_Date','Customer_Fail_Desc',
    'Problem','Inspection','Repair_Action','All_Location',
    'Main_Location','Part_Type','Part_P_N','Part_Name','Repair_result',
    'Repair_Level','Rootcause','Complete_PN','Complete_SN',
    'Repeat_days','Repeat_days_ww','Repeat_days_fwd_ww','RD_fwd_ww_NR',
    'Repair_Start_Date','Repair_End_date','Return',
    'Return_Approve_Status','Remark_Return','Remark_Pickup',
    'Remark_Receiving','Remark_Shipping','Remark_ATH','Remark_IW',
    'Dismatch','Dismatch_Approve_Status','Remark_Dismatch','Remark',
    'Stage','Date_of_submitting_shipment_data',
    'Date_of_submitting_vendor_receiving_date',
    'Date_of_submitting_vendor_shipping_date',
    'Date_of_submitting_vendor_Arrive_to_HUB_data','Pick_up_TAT',
    'Receiving_TAT','RTP','Max_Repair_times','Next_Repair_ID',
    'Last_Repair_ID','QH_Start_Date','QH_End_Date','QI','New_Geo','Shipping_Date_Month','Week_NUM','New_Commodity']
    
    # 对指定目录进行csv的遍历
    for each in file_list:
        if '.csv' in each:
            #//10.103.66.10/TeamWork-SBOMECSQ/NEW-RAW-data/
            data=pd.read_csv('//10.103.66.10/TeamWork-SBOMECSQ/20190730data/'+each,sep=',',encoding='gbk',keep_default_na=False)
            data.columns=cols
            print('%s shape is %s'%(each,data.shape[0]))
            data.to_sql('LGRS_Repair_KPI_Raw_Data_New_C',con=engine,index=False,if_exists='append',chunksize=1000)
            print('%s 传输完毕'%each)

### 注意事项 ###

对csv文件读取 有多个scv文件
    	首先要保证csv的字段都是一致的
    	
    	对数据库写入时可以 先建立一张空表 
    		指定空表的字段和数据类型
    			要求数据类型能够匹配每一个csv的文件

## 生成新的字段 ##

use Repair_KPI_New
    
    --生成New_Geo
    -- alter table Repari_KPI_New.dbo.LGRS_Repair_KPI_Raw_Data_New_C add New_Geo varchar(max)
    go
    update LGRS_Repair_KPI_Raw_Data_New_C set New_Geo='India' where Customer='AP_ICT_HUB'
    update LGRS_Repair_KPI_Raw_Data_New_C set New_Geo='Canada' where Customer='NA_CA_HUB'
    update LGRS_Repair_KPI_Raw_Data_New_C set New_Geo=Geo where New_GEO = ' '
    -- 生成Month字段
    -- alter table Repari_KPI_New.dbo.LGRS_Repair_KPI_Raw_Data_New_C add Receiving_Date_Month varchar(max) null
    go
    update LGRS_Repair_KPI_Raw_Data_New_C set Receiving_Date_Month=convert(varchar,year(Receiving_Date))+'-'+convert(varchar,Month(Receiving_Date))
    
    --生成Shipping_Date_Month
    -- alter table Repari_KPI_New.dbo.LGRS_Repair_KPI_Raw_Data_New_C add Shipping_Date_Month varchar(max) null
    go
    update LGRS_Repair_KPI_Raw_Data_New_C set Receiving_Date_Month=convert(varchar,year(Shipping_Date))+'-'+convert(varchar,Month(Shipping_Date))
    
    -- alter table Repari_KPI_New.dbo.LGRS_Repair_KPI_Raw_Data_New_C add Week_NUM varchar(max) null
    go
    update LGRS_Repair_KPI_Raw_Data_New_C set Week_NUM=convert(varchar(max),year(convert(date,Receiving_Date,110)))+'-'+convert(varchar(max),datepart(wk,Receiving_Date))
    
    -- alter table Repari_KPI_New.dbo.LGRS_Repair_KPI_Raw_Data_New_C add New_Commodity varchar(max)
    go
    update LGRS_Repair_KPI_Raw_Data_New_C set New_Commodity=p_Commodity
    
    
    -- select distinct p_commodity from LGRS_Repair_KPI_Raw_Data_NEW

## 对每一个KPI进行计算 ##

--C1 Total_shippment
    select Week_Num,Shipping_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Total_shippment
    into C1
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere Stage>=3 and Result in ('GOOD','SWAP')
     group by Week_Num,Shipping_Date_Month,New_Commodity,New_Geo,vendor
     
    go
     --C2 计算分commdity计算RR
    select t.* into C2 from (
    select Week_Num,Shipping_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Repeat_Repair_Qty
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere RD_fwd_ww_NR>0 and RD_fwd_ww_NR<=120 and Result in ('GOOD','SWAP') and New_Commodity not in ('LCD','LCD_Tab')
     group by Week_Num,Shipping_Date_Month,New_Commodity,New_Geo,Vendor
     union ALL
     select Week_Num,Shipping_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Repeat_Repair_Qty
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere  RD_fwd_ww_NR>0 and RD_fwd_ww_NR<=180 and Result in ('GOOD','SWAP') and New_Commodity  in ('LCD','LCD_Tab')
     group by Week_Num,Shipping_Date_Month,New_Commodity,New_Geo,Vendor
     )t
     
     go
     --C3
      -- 计算RR rate
    select  A.Shipping_Date_Month,A.New_Commodity,A.New_Geo,A.Vendor,A.Total_shippment,B.Repeat_Repair_Qty,
    convert(float,B.Repeat_Repair_Qty)/A.Total_shippment as RRR_Rate
    into C3
    from C1 A left join C2 B on A.New_Commodity=B.New_Commodity and A.New_Geo=B.New_Geo and A.Shipping_Date_Month=B.Shipping_Date_Month 
    and A.Vendor=B.Vendor
    
    update C3 set Repeat_Repair_Qty=0 where Repeat_Repair_Qty is null 
    update C3 set RRR_Rate=0 where RRR_Rate is null 
    
    go
    --C4
    -- 计算Received Qty
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Received_Qty
    into C4
     from LGRS_Repair_KPI_Raw_Data_New_C
     where Stage>1
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     
     --C5
      -- 计算Received_in_Month_and_Repair_Good
    go
     select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Received_in_Month_and_Repair_Good
    into C5
     from LGRS_Repair_KPI_Raw_Data_New_C
     where Stage>=3 and Result in ('Return','RETURN')
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     
     go
     --C6
      -- 计算Repair Good and shipped qty
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Repair_Good_and_shipped_Qty
    into C6
    from LGRS_Repair_KPI_Raw_Data_New_C
    where Stage>=3 and Result in ('GOOD','SWAP')
    group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
    go
    --C7
     -- 计算SCRAP_Qty
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as SCRAP_Qty
    into C7
    from LGRS_Repair_KPI_Raw_Data_New_C
    where Result in ('SCRAP')
    group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
    
    go
    --C8
     -- 计算WIP
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as WIP
    into C8
    from LGRS_Repair_KPI_Raw_Data_New_C
    where Stage=2
    group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
    
    go
    --C9 TAT_Qty
    select t.* into C9 from (
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as TAT_Qty
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere Receiving_TAT<=10 and Result in ('GOOD','SWAP') and New_Commodity not in ('LCD','LCD_Tab')
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     union ALL
     select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as TAT_Qty
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere Receiving_TAT<=12 and Result in ('GOOD','SWAP') and New_Commodity  in ('LCD','LCD_Tab')
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     )t
     
     go
     --C10 AVG_TAT AVG_TAT_Amount
     select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,avg(Receiving_TAT + 0.0) as AVG_TAT,count(*) as AVG_TAT_Amount
    into C10
    from LGRS_Repair_KPI_Raw_Data_New_C
    where Result in ('GOOD','SWAP')
    group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
    
    go
    --C11
    --分commodity计算overdue
    select t.* into C11 from (
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Overdue
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere Receiving_TAT>10 and New_Commodity not in ('LCD','LCD_Tab')
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     union ALL
     select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Overdue
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere Receiving_TAT>12  and New_Commodity  in ('LCD','LCD_Tab')
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     )t
     go
     --C12
     -- 计算Return_Qty
    
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as Return_Qty
    into C12
    from LGRS_Repair_KPI_Raw_Data_New_C
    where Result='RETURN'
    group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
    go
    --C14
    -- 计算分commdity计算With_TAT_Qty
    select t.* into C14 from (
    select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as With_TAT_Qty
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere Receiving_TAT<=10  and New_Commodity not in ('LCD','LCD_Tab')
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     union ALL
     select Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor,count(*) as With_TAT_Qty
     from LGRS_Repair_KPI_Raw_Data_New_C
      WHere Receiving_TAT<=12  and New_Commodity  in ('LCD','LCD_Tab')
     group by Week_Num,Receiving_Date_Month,New_Commodity,New_Geo,Vendor
     )t
     go
     --C13
     --生成组合排列
    select * into C13 from (select distinct Receiving_Date_Month from LGRS_Repair_KPI_Raw_Data_New_C)a 
    CROSS join (select distinct New_Commodity from LGRS_Repair_KPI_Raw_Data_New_C)b
    CROSS join (select distinct New_Geo from LGRS_Repair_KPI_Raw_Data_New_C)c
    CROSS join (select distinct vendor from LGRS_Repair_KPI_Raw_Data_New_C)d
    CROSS join (select distinct Week_NUM from LGRS_Repair_KPI_Raw_Data_New_C)e

## 对所有的KPI进行一个整合 ##

select C13.Week_NUM,C13.Receiving_Date_Month as Date_Month,C13.New_Geo,C13.New_Commodity,C13.Vendor,
    C1.Total_shippment,
    C2.Repeat_Repair_Qty,C4.Received_Qty,
    C5.Received_in_Month_and_Repair_Good,
    C6.Repair_Good_and_shipped_Qty,
    C7.SCRAP_Qty,
    C8.WIP,
    C9.TAT_Qty,C10.AVG_TAT,C10.AVG_TAT_Amount,C11.Overdue,C12.Return_Qty,
    C14.With_TAT_Qty
    into LGRS_KPI_C
    from C13
    left join C4 on C13.Receiving_Date_Month=C4.receiving_date_month and C13.New_Geo=C4.New_Geo and C13.Vendor=C4.Vendor and C13.New_Commodity=C4.New_Commodity and C13.Week_NUM=C4.Week_NUM
    left join C1 on C13.Receiving_Date_Month=C1.Shipping_Date_Month and C13.New_Geo=C1.New_Geo and C13.Vendor=C1.Vendor and C13.New_Commodity=C1.New_Commodity and C13.Week_NUM=C1.Week_NUM
    left join C2 on C13.Receiving_Date_Month=C2.Shipping_Date_Month and C13.New_Geo=C2.New_Geo and C13.Vendor=C2.Vendor  and C13.New_Commodity=C2.New_Commodity and C13.Week_NUM=C2.Week_NUM
    left join C5 on  C13.Receiving_Date_Month=C5.Receiving_Date_Month and C13.New_Geo=C5.New_Geo and C13.Vendor=C5.Vendor  and C13.New_Commodity=C5.New_Commodity and C13.Week_NUM=C5.Week_NUM
    left join C6 on C13.Receiving_Date_Month=C6.Receiving_Date_Month and C13.New_Geo=C6.New_Geo and C13.Vendor=C6.Vendor  and C13.New_Commodity=C6.New_Commodity and C13.Week_NUM=C6.Week_NUM
    left join C7 on C13.Receiving_Date_Month=C7.Receiving_Date_Month and C13.New_Geo=C7.New_Geo and C13.Vendor=C7.Vendor  and C13.New_Commodity=C7.New_Commodity and C13.Week_NUM=C7.Week_NUM
    left join C8 on C13.Receiving_Date_Month=C8.Receiving_Date_Month and C13.New_Geo=C8.New_Geo and C13.Vendor=C8.Vendor  and C13.New_Commodity=C8.New_Commodity and C13.Week_NUM=C8.Week_NUM
    left join C9 on C13.Receiving_Date_Month=C9.Receiving_Date_Month and C13.New_Geo=C9.New_Geo and C13.Vendor=C9.Vendor  and C13.New_Commodity=C9.New_Commodity and C13.Week_NUM=C9.Week_NUM
    left join C10 on C13.Receiving_Date_Month=C10.Receiving_Date_Month and C13.New_Geo=C10.New_Geo and C13.Vendor=C10.Vendor  and C13.New_Commodity=C10.New_Commodity and C13.Week_NUM=C10.Week_NUM
    left join C11 on C13.Receiving_Date_Month=C11.Receiving_Date_Month and C13.New_Geo=C11.New_Geo and C13.Vendor=C11.Vendor  and C13.New_Commodity=C11.New_Commodity and C13.Week_NUM=C11.Week_NUM
    left join C12 on C13.Receiving_Date_Month=C12.Receiving_Date_Month and C13.New_Geo=C12.New_Geo and C13.Vendor=C12.Vendor  and C13.New_Commodity=C12.New_Commodity and C13.Week_NUM=C12.Week_NUM
    left join C14 on C13.Receiving_Date_Month=C14.Receiving_Date_Month and C13.New_Geo=C14.New_Geo and C13.Vendor=C14.Vendor  and C13.New_Commodity=C14.New_Commodity and C13.Week_NUM=C14.Week_NUM

# Qlik端的导入操作 #

## Qlik数据加载编辑器 ##

SET ThousandSep=',';
    SET DecimalSep='.';
    SET MoneyThousandSep=',';
    SET MoneyDecimalSep='.';
    SET MoneyFormat='¥#,##0.00;-¥#,##0.00';
    SET TimeFormat='TTh:mm:ss';
    SET DateFormat='YYYY/MM/DD';
    SET TimestampFormat='YYYY/M/D TTh:mm:ss[.fff]';
    SET FirstWeekDay=6;
    SET BrokenWeeks=1;
    SET ReferenceDay=0;
    SET FirstMonthOfYear=1;
    SET CollationLocale='zh-CN';
    SET CreateSearchIndexOnReload=1;
    SET MonthNames='1;2;3;4;5;6;7;8;9;10;11;12';
    SET LongMonthNames='一月;二月;三月;四月;五月;六月;七月;八月;九月;十月;十一月;十二月';
    SET DayNames='周一;周二;周三;周四;周五;周六;周日';
    SET LongDayNames='星期一;星期二;星期三;星期四;星期五;星期六;星期日';
    SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
    LIB CONNECT TO '10.103.66.45,1444';
    
    LOAD "Receiving_Date_Month",
    	"Week_Num",
        "New_Geo",
        "New_Commodity",
        Vendor,
        "Total_shippment",
        "Repeat_Repair_Qty",
        "Received_Qty",
        "Repair_Good_and_shipped_Qty",
        "SCRAP_Qty",
        WIP,
        "TAT_Qty",
        "AVG_TAT",
        "AVG_TAT_Amount",
        Overdue,
        "Return_Qty",
        "With_TAT_Qty";
    SQL SELECT "Receiving_Date_Month",
    	"Week_Num",
        "New_Geo",
        "New_Commodity",
        Vendor,
        "Total_shippment",
        "Repeat_Repair_Qty",
        "Received_Qty",
        "Repair_Good_and_shipped_Qty",
        "SCRAP_Qty",
        WIP,
        "TAT_Qty",
        "AVG_TAT",
        "AVG_TAT_Amount",
        Overdue,
        "Return_Qty",
        "With_TAT_Qty"
    FROM "test".dbo."Final_Final"
    where New_Geo not like '%PRC%';

# 效果展示 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70]: /images/20211114/fdb8eed9a7274737b5cc1cf22b2e744a.png