Data Cleaning while loading in qlik

Aspiring_Developer — Fri, 21 Feb 2020 08:59:02 GMT

Hi Team,

Please find attached the data set and please refer the "Quality Perfo Dashboard-Yearly " tab in excel.

I wish to get the below dimesnions:-

->Official Customer PPM:-if(volume shipped=0, """, (NOK Parts in Customer Database Total/ Volume Shipped)*1000,000

->Site PPM Performance:-if (volume shipped=0,"", (Real NOK Parts Manufactured-Total/Volume Shipped)*1000,000

->Official Customer IPB:-if(Volume shipped=0,"", (Number of official written concern/ Volume Shipped)*1000,000,000

->Site Performance IPB:- if(Volume Shipped=0,"", (Total Number Alert + Written Concern /Volume Shipped)*1000,000,000

I wish to get the below output :-

	Official customer PPM	Site PPM performance	Official customer IPB	Site IPB performance
Apr-19	29	698	12982	22224
May-19	153	732	9333	23035
Jun-19	42	568	9549	22919
Jul-19	109	669	8401	29523
Aug-19	110	489	6169	20188
Sep-19	145	504	5464	20350
Oct-19	16	349	8718	30304
Nov-19	48	548	5118	19903
Dec-19	14	357	6346	25385
Jan-20	58	994	6133	22692
Feb-20	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Mar-20	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!

Can anyone please help ?

Re: Data Cleaning while loading in qlik

johngouws — Fri, 21 Feb 2020 06:16:39 GMT

Hi.

I looked at your spreadsheet. It seems to have the output you require. Do you have example data to work with?

John

Re: Data Cleaning while loading in qlik

Aspiring_Developer — Fri, 21 Feb 2020 08:57:48 GMT

Hi John,

Please check "Quality Perfo Dashboard-Yearly " excel tab. I do not have these dimensions there and i want the output on the basis of this particular tab .

Re: Data Cleaning while loading in qlik

Aspiring_Developer — Fri, 21 Feb 2020 10:13:01 GMT

Hi Everyone, So far i have done the below :-

Data:
First 2
LOAD
"VLS Global",
F2 as [NOK parts in customer database - total_Apr],
F3 as [Real NOK parts manufactured - total_Apr],
F4 as [Number of official Written concern_Apr],
F5 as [Total number Alert + Written concern_Apr],
F6 as [Volumes shipped (external sales only)_Apr],
F7 as [NOK parts in customer database - total_May],
F8 as [Real NOK parts manufactured - total_May],
F9 as [Number of official Written concern_May],
F10 as [Total number Alert + Written concern_May],
F11 as [Volumes shipped (external sales only)_May],
F12 as [NOK parts in customer database - total_Jun],
F13 as [Real NOK parts manufactured - total_Jun],
F14 as [Number of official Written concern_Jun],
F15 as [Total number Alert + Written concern_Jun],
F16 as [Volumes shipped (external sales only)_Jun],
F17 as [NOK parts in customer database - total_Jul],
F18 as [Real NOK parts manufactured - total_Jul],
F19 as [Number of official Written concern_Jul],
F20 as [Total number Alert + Written concern_Jul],
F21 as [Volumes shipped (external sales only)_Jul],
F22 as [NOK parts in customer database - total_Aug],
F23 as [Real NOK parts manufactured - total_Aug],
F24 as [Number of official Written concern_Aug],
F25 as [Total number Alert + Written concern_Aug],
F26 as [Volumes shipped (external sales only)_Aug],
F27 as [NOK parts in customer database - total_Sep],
F28 as [Real NOK parts manufactured - total_Sep],
F29 as [Number of official Written concern_Sep],
F30 as [Total number Alert + Written concern_Sep],
F31 as [Volumes shipped (external sales only)_Sep],
F32 as [NOK parts in customer database - total_Oct],
F33 as [Real NOK parts manufactured - total_Oct],
F34 as [Number of official Written concern_Oct],
F35 as [Total number Alert + Written concern_Oct],
F36 as [Volumes shipped (external sales only)_Oct],
F37 as [NOK parts in customer database - total_Nov],
F38 as [Real NOK parts manufactured - total_Nov],
F39 as [Number of official Written concern_Nov],
F40 as [Total number Alert + Written concern_Nov],
F41 as [Volumes shipped (external sales only)_Nov],
F42 as [NOK parts in customer database - total_Dec],
F43 as [Real NOK parts manufactured - total_Dec],
F44 as [Number of official Written concern_Dec],
F45 as [Total number Alert + Written concern_Dec],
F46 as [Volumes shipped (external sales only)_Dec],
F47 as [NOK parts in customer database - total_Jan],
F48 as [Real NOK parts manufactured - total_Jan],
F49 as [Number of official Written concern_Jan],
F50 as [Total number Alert + Written concern_Jan],
F51 as [Volumes shipped (external sales only)_Jan],
F52 as [NOK parts in customer database - total_Feb],
F53 as [Real NOK parts manufactured - total_Feb],
F54 as [Number of official Written concern_Feb],
F55 as [Total number Alert + Written concern_Feb],
F56 as [Volumes shipped (external sales only)_Feb],
F57 as [NOK parts in customer database - total_Mar],
F58 as [Real NOK parts manufactured - total_Mar],
F59 as [Number of official Written concern_Mar],
F60 as [Total number Alert + Written concern_Mar],
F61 as [Volumes shipped (external sales only)_Mar]
FROM [lib://Temp_Dhiloj_Request (varpwqlkdev1_visguest)/ZZ Global January 2019.xlsx]
(ooxml, embedded labels, table is [Quality Perfo Dashboard-Yearly ], filters (

Remove(Row, Pos(Top, 2))

))
where "VLS Global"= 'TOTAL ACTUAL'
;

store * from Data into [lib://Temp_Dhiloj_Request (varpwqlkdev1_visguest)/Data.qvd](qvd);

drop table Data;

New_Data:
Load

//official Customer PPM

if([Volumes shipped (external sales only)_Apr]=0,'0',

([NOK parts in customer database - total_Apr]/[Volumes shipped (external sales only)_Apr])*1000000
) as Official_Customer_PPM_Apr,

if([Volumes shipped (external sales only)_May]=0,'0',

([NOK parts in customer database - total_May]/[Volumes shipped (external sales only)_May])*1000000
) as Official_Customer_PPM_May,

if([Volumes shipped (external sales only)_Jun]=0,'0',

( [NOK parts in customer database - total_Jun]/[Volumes shipped (external sales only)_Jun])*1000000
) as Official_Customer_PPM_Jun,

if([Volumes shipped (external sales only)_Jul]=0,'0',

( [NOK parts in customer database - total_Jul]/[Volumes shipped (external sales only)_Jul])*1000000
) as Official_Customer_PPM_Jul,

if([Volumes shipped (external sales only)_Aug]=0,'0',

( [NOK parts in customer database - total_Aug]/[Volumes shipped (external sales only)_Aug])*1000000
) as Official_Customer_PPM_Aug,

if([Volumes shipped (external sales only)_Sep]=0,'0',

( [NOK parts in customer database - total_Sep]/[Volumes shipped (external sales only)_Sep])*1000000
) as Official_Customer_PPM_Sep,

if([Volumes shipped (external sales only)_Oct]=0,'0',

( [NOK parts in customer database - total_Oct]/[Volumes shipped (external sales only)_Oct])*1000000
) as Official_Customer_PPM_Oct,

if([Volumes shipped (external sales only)_Nov]=0,'0',

( [NOK parts in customer database - total_Nov]/[Volumes shipped (external sales only)_Nov])*1000000
) as Official_Customer_PPM_Oct,

if([Volumes shipped (external sales only)_Dec]=0,'0',

( [NOK parts in customer database - total_Dec]/[Volumes shipped (external sales only)_Dec])*1000000
) as Official_Customer_PPM_Dec,

if([Volumes shipped (external sales only)_Jan]=0,'0',

( [NOK parts in customer database - total_Jan]/[Volumes shipped (external sales only)_Jan])*1000000
) as Official_Customer_PPM_Jan,

if([Volumes shipped (external sales only)_Feb]=0,'0',

( [NOK parts in customer database - total_Feb]/[Volumes shipped (external sales only)_Feb])*1000000
) as Official_Customer_PPM_Feb,

if([Volumes shipped (external sales only)_Mar]=0,'0',

( [NOK parts in customer database - total_Mar]/[Volumes shipped (external sales only)_Mar])*1000000
) as Official_Customer_PPM_Mar,

//Site PPM Performance
if([Volumes shipped (external sales only)_Apr]=0,'0',

([Real NOK parts manufactured - total_Apr]/[Volumes shipped (external sales only)_Apr])*1000000
) as Site_PPM_Performance_Apr,

if([Volumes shipped (external sales only)_May]=0,'0',

([Real NOK parts manufactured - total_May]/[Volumes shipped (external sales only)_May])*1000000
) as Site_PPM_Performance_May,

if([Volumes shipped (external sales only)_Jun]=0,'0',

([Real NOK parts manufactured - total_Jun]/[Volumes shipped (external sales only)_Jun])*1000000
) as Site_PPM_Performance_Jun,

if([Volumes shipped (external sales only)_Jul]=0,'0',

([Real NOK parts manufactured - total_Jul]/[Volumes shipped (external sales only)_Jul])*1000000
) as Site_PPM_Performance_Jul,

if([Volumes shipped (external sales only)_Aug]=0,'0',

([Real NOK parts manufactured - total_Aug]/[Volumes shipped (external sales only)_Aug])*1000000
) as Site_PPM_Performance_Aug,

if([Volumes shipped (external sales only)_Sep]=0,'0',

([Real NOK parts manufactured - total_Sep]/[Volumes shipped (external sales only)_Sep])*1000000
) as Site_PPM_Performance_Sep,

if([Volumes shipped (external sales only)_Oct]=0,'0',

([Real NOK parts manufactured - total_Oct]/[Volumes shipped (external sales only)_Oct])*1000000
) as Site_PPM_Performance_Oct,

if([Volumes shipped (external sales only)_Nov]=0,'0',

([Real NOK parts manufactured - total_Nov]/[Volumes shipped (external sales only)_Nov])*1000000
) as Site_PPM_Performance_Nov,

if([Volumes shipped (external sales only)_Dec]=0,'0',

([Real NOK parts manufactured - total_Dec]/[Volumes shipped (external sales only)_Dec])*1000000
) as Site_PPM_Performance_Dec,

if([Volumes shipped (external sales only)_Jan]=0,'0',

([Real NOK parts manufactured - total_Jan]/[Volumes shipped (external sales only)_Jan])*1000000
) as Site_PPM_Performance_Jan,

if([Volumes shipped (external sales only)_Feb]=0,'0',

([Real NOK parts manufactured - total_Feb]/[Volumes shipped (external sales only)_Feb])*1000000
) as Site_PPM_Performance_Feb,

if([Volumes shipped (external sales only)_Mar]=0,'0',

([Real NOK parts manufactured - total_Mar]/[Volumes shipped (external sales only)_Mar])*1000000
) as Site_PPM_Performance_Mar,

//Official Customer IPB

if([Volumes shipped (external sales only)_Apr]=0,'0',

([Number of official Written concern_Apr]/[Volumes shipped (external sales only)_Apr])*1000000000
) as Official_Customer_IPB_Apr,

if([Volumes shipped (external sales only)_May]=0,'0',

([Number of official Written concern_May]/[Volumes shipped (external sales only)_May])*1000000000
) as Official_Customer_IPB_May,

if([Volumes shipped (external sales only)_Jun]=0,'0',

([Number of official Written concern_Jun]/[Volumes shipped (external sales only)_Jun])*1000000000
) as Official_Customer_IPB_Jun,

if([Volumes shipped (external sales only)_Jul]=0,'0',

([Number of official Written concern_Jul]/[Volumes shipped (external sales only)_Jul])*1000000000
) as Official_Customer_IPB_Jul,

if( [Volumes shipped (external sales only)_Aug]=0,'0',

( [Number of official Written concern_Aug]/[Volumes shipped (external sales only)_Aug])*1000000000
) as Official_Customer_IPB_Aug,

if( [Volumes shipped (external sales only)_Sep]=0,'0',

( [Number of official Written concern_Sep]/[Volumes shipped (external sales only)_Sep])*1000000000
) as Official_Customer_IPB_Sep,

if( [Volumes shipped (external sales only)_Oct]=0,'0',

( [Number of official Written concern_Oct]/[Volumes shipped (external sales only)_Oct])*1000000000
) as Official_Customer_IPB_Oct,

if( [Volumes shipped (external sales only)_Nov]=0,'0',

( [Number of official Written concern_Nov]/[Volumes shipped (external sales only)_Nov])*1000000000
) as Official_Customer_IPB_Nov,

if( [Volumes shipped (external sales only)_Dec]=0,'0',

( [Number of official Written concern_Dec]/[Volumes shipped (external sales only)_Dec])*1000000000
) as Official_Customer_IPB_Dec,

if( [Volumes shipped (external sales only)_Jan]=0,'0',

( [Number of official Written concern_Jan]/[Volumes shipped (external sales only)_Jan])*1000000000
) as Official_Customer_IPB_Jan,

if( [Volumes shipped (external sales only)_Feb]=0,'0',

( [Number of official Written concern_Feb]/[Volumes shipped (external sales only)_Feb])*1000000000
) as Official_Customer_IPB_Feb,

if( [Volumes shipped (external sales only)_Mar]=0,'0',

( [Number of official Written concern_Mar]/[Volumes shipped (external sales only)_Mar])*1000000000
) as Official_Customer_IPB_Mar
,
//Site Performace IPB

if( [Volumes shipped (external sales only)_Apr]=0,'0',

( [Total number Alert + Written concern_Apr]/[Volumes shipped (external sales only)_Apr])*1000000000
) as Site_Performance_IPB_Apr,

if( [Volumes shipped (external sales only)_May]=0,'0',

( [Total number Alert + Written concern_May]/[Volumes shipped (external sales only)_May])*1000000000
) as Site_Performance_IPB_May,

if( [Volumes shipped (external sales only)_Jun]=0,'0',

( [Total number Alert + Written concern_Jun]/[Volumes shipped (external sales only)_Jun])*1000000000
) as Site_Performance_IPB_Jun,

if( [Volumes shipped (external sales only)_Jul]=0,'0',

( [Total number Alert + Written concern_Jul]/[Volumes shipped (external sales only)_Jul])*1000000000
) as Site_Performance_IPB_Jul,

if( [Volumes shipped (external sales only)_Aug]=0,'0',

( [Total number Alert + Written concern_Aug]/[Volumes shipped (external sales only)_Aug])*1000000000
) as Site_Performance_IPB_Aug,

if( [Volumes shipped (external sales only)_Sep]=0,'0',

( [Total number Alert + Written concern_Sep]/[Volumes shipped (external sales only)_Sep])*1000000000
) as Site_Performance_IPB_Sep,

if( [Volumes shipped (external sales only)_Oct]=0,'0',

( [Total number Alert + Written concern_Oct]/[Volumes shipped (external sales only)_Oct])*1000000000
) as Site_Performance_IPB_Oct,

if( [Volumes shipped (external sales only)_Nov]=0,'0',

( [Total number Alert + Written concern_Nov]/[Volumes shipped (external sales only)_Nov])*1000000000
) as Site_Performance_IPB_Nov,

if( [Volumes shipped (external sales only)_Dec]=0,'0',

( [Total number Alert + Written concern_Dec]/[Volumes shipped (external sales only)_Dec])*1000000000
) as Site_Performance_IPB_Dec,

if( [Volumes shipped (external sales only)_Jan]=0,'0',

( [Total number Alert + Written concern_Jan]/[Volumes shipped (external sales only)_Jan])*1000000000
) as Site_Performance_IPB_Jan,

if( [Volumes shipped (external sales only)_Feb]=0,'0',

( [Total number Alert + Written concern_Feb]/[Volumes shipped (external sales only)_Feb])*1000000000
) as Site_Performance_IPB_Feb,

if( [Volumes shipped (external sales only)_Mar]=0,'0',

( [Total number Alert + Written concern_Mar]/[Volumes shipped (external sales only)_Mar])*1000000000
) as Site_Performance_IPB_Mar

Resident Data;
Drop table Data;

I am using a lot of if statements at the script level. Can anyone please suggest me something better.

Also i should i get the date . Please refer to "Quality Perfo Dashboard-Yearly " in the attached for analyzing my request.

Thanks in Advance

topic Re: Data Cleaning while loading in qlik in App Development

Data Cleaning while loading in qlik

Re: Data Cleaning while loading in qlik

Re: Data Cleaning while loading in qlik

Re: Data Cleaning while loading in qlik