Data cleaning while loading in qlik

Aspiring_Developer · ‎2020-02-24

Hi Team,

I have the below data set :-

VLS Global

Customers	April-19	May-19	June-19	July-19	August-19	September-19	October-19	November-19	December-19	January-20	February-20	March-20
NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Unofficial Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Written concern	Volumes shipped (external sales only)
Brazil	3	34	1	3	9,780	0	0	0	1	18,540	0	0	0	1	14,940	0	1	0	1	16,080	360	360	1	1	17,550	0	0	0	1	14,400	0	0	0	0	13,560	0	0	0	0	15,150	0	0	0	0	6,720	0	0	0	1	15,722	0	0	0	0	0	0	0	0	0	0
Bulgaria	0	0	2	2	66,300	0	0	0	0	66,247	0	0	1	1	58,524	2	2	2	2	31,413	0	0	0	0	42,678	0	0	0	0	55,928	0	0	0	0	56,031	0	0	0	0	67,833	1	3	1	1	32,529	92	192	3	3	1,21,395	0	0	0	0	0	0	0	0	0	0
India Pune	4	482	3	3	78,220	2	477	2	2	81,296	6	291	4	4	75,567	4	230	2	2	76,701	4	188	2	2	64,348	5	155	3	4	66,408	2	304	1	1	66,055	2	594	1	1	67,963	6	553	2	2	79,107	12	1,149	3	3	1,29,007	0	0	0	0	0	0	0	0	0	0
Italy	22	25	22	25	90,727	266	266	8	8	1,60,454	99	99	8	8	1,29,181	94	94	3	3	1,22,702	0	12	0	5	48,759	9	65	3	10	1,57,680	14	54	5	14	1,06,100	1	60	1	5	90,640	0	1	0	1	66,798	0	79	1	4	1,89,860	0	0	0	0	0	0	0	0	0	0
Mexico	60	1,991	2	9	3,98,365	6	1,216	5	18	5,03,689	6	956	2	11	4,41,184	2	875	2	9	3,97,019	1	398	1	19	3,82,425	2	217	0	15	4,08,460	26	355	6	18	4,26,588	1	255	1	9	4,04,869	2	151	2	8	3,12,553	12	152	2	5	3,52,511	0	0	0	0	0	0	0	0	0	0
Morocco	0	95	0	0	25,468	0	68	0	0	32,506	2	34	0	2	49,553	1	55	1	1	59,620	0	9	0	0	28,168	0	185	0	0	45,733	4	70	4	4	68,125	0	42	0	0	53,343	7	94	2	2	56,074	0	40	0	0	81,483	0	0	0	0	0	0	0	0	0	0
Novy Jicin	27	481	15	34	9,33,556	21	988	21	53	10,17,364	13	635	10	37	8,49,793	12	1,008	7	59	6,40,068	13	456	6	17	6,28,021	8	795	8	30	9,51,843	8	677	7	41	9,57,245	7	1,261	7	47	8,57,426	10	477	6	37	6,59,019	157	2,884	11	37	8,75,779	0	0	0	0	0	0	0	0	0	0
Rychvald	3	45	3	14	1,88,561	6	63	3	23	2,43,949	4	148	4	15	2,15,828	10	184	6	24	1,89,929	3	20	3	13	1,65,309	13	54	9	33	2,62,372	10	56	10	27	3,22,985	12	31	5	18	3,05,424	3	58	3	11	2,30,734	6	167	6	27	3,23,616	0	0	0	0	0	0	0	0	0	0
Romania	0	0	1	1	2,72,355	0	10	0	2	3,19,040	0	2	0	1	2,61,607	0	1	0	1	3,18,170	0	0	0	0	1,15,039	714	902	3	4	3,19,730	2	2	2	2	2,16,981	0	3	0	2	2,72,213	0	0	0	0	1,08,604	0	0	0	0	2,73,642	0	0	0	0	0	0	0	0	0	0
Poland	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	129	2	29	3,248	0	182	0	26	14,923	0	0	0	0	0	0	0	0	0	0
Turkey	6	6	4	4	21,49,175	362	387	1	2	23,23,812	6	6	4	4	17,69,565	319	319	4	6	19,77,009	0	178	1	1	17,91,943	18	300	2	8	26,33,696	8	132	4	6	21,84,676	28	443	6	17	26,43,949	16	27	4	6	22,88,836	6	17	3	4	23,99,265	0	0	0	0	0	0	0	0	0	0
Vietnam	1	1	1	1	78,699	107	209	7	7	72,513	30	30	2	2	86,870	2	2	2	2	79,711	4	4	3	3	78,405	1	1	1	1	84,290	1	1	1	1	70,895	195	195	1	1	87,071	7	7	2	3	87,395	1	1	1	1	1,14,358	0	0	0	0	0	0	0	0	0	0
VTYC CQ	3	8	3	3	1,50,985	0	0	0	0	86,839	7	174	4	7	1,51,333	4	9	2	4	1,60,170	6	114	3	9	1,03,708	0	0	0	0	1,88,943	2	31	2	31	2,23,373	8	8	4	4	2,61,951	2	2	2	2	2,28,203	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
VTYC CZ	5	5	2	2	1,02,486	0	4	0	0	1,09,470	1	3	1	3	84,792	3	9	4	9	97,609	2	4	2	2	1,00,146	0	2	0	2	1,17,626	0	1	0	1	1,05,194	1	1	1	1	1,47,849	1	17	1	6	94,689	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
India Chennai	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Europe (NJ,RY,PL, Mor.)	30	621	18	48	11,47,585	27	1,119	24	76	12,93,819	19	817	14	54	0	23	1,247	14	84	8,89,617	16	485	9	30	8,21,498	21	1,034	17	63	12,59,948	22	803	21	72	13,48,355	19	1,334	12	65	12,16,193	23	758	13	79	9,49,075	163	3,273	17	90	12,95,801	0	0	0	0	0	0	0	0	0	0
US (Brazil+ Mexico)	63	2,025	3	12	4,08,145	6	1,216	5	19	5,22,229	6	956	2	12	0	2	876	2	10	4,13,099	361	758	2	20	3,99,975	2	217	0	16	4,22,860	26	355	6	18	4,40,148	1	255	1	9	4,20,019	2	151	2	8	3,19,273	12	152	2	6	3,68,233	0	0	0	0	0	0	0	0	0	0
2WSL	29	35	30	33	26,57,256	735	872	16	19	29,42,066	135	137	15	16	0	417	418	11	14	25,29,005	4	194	4	9	20,76,824	742	1,268	9	23	32,51,324	25	189	12	23	26,34,683	224	701	8	25	31,61,706	24	38	7	11	25,84,162	99	289	8	12	30,98,520	0	0	0	0	0	0	0	0	0	0
China	8	13	5	5	2,70,858	0	4	0	0	1,96,309	8	177	5	10	0	7	18	6	13	2,57,779	8	118	5	11	2,03,854	0	2	0	2	3,06,569	2	32	2	32	3,28,567	9	9	5	5	4,09,800	3	19	3	8	3,22,892	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
India (Pune+ chennai)	4	482	3	3	78,220	2	477	2	2	81,296	6	291	4	4	0	4	230	2	2	76,701	4	188	2	2	64,348	5	155	3	4	66,408	2	304	1	1	66,055	2	594	1	1	67,963	6	553	2	2	79,107	12	1,149	3	3	1,29,007	0	0	0	0	0	0	0	0	0	0
	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
TOTAL ACTUAL	134	3,173	59	101	45,44,677	770	3,688	47	116	50,35,719	174	2,378	40	96	41,88,737	453	2,789	35	123	41,66,201	393	1,743	22	72	35,66,499	770	2,676	29	108	53,07,109	77	1,683	42	146	48,17,808	255	2,893	27	105	52,75,681	58	1,519	27	108	42,54,509	286	4,863	30	111	48,91,561	0	0	0	0	0	0	0	0	0	0

I am trying to get the below output :-

	NOK parts in customer database - total	Real NOK parts manufactured - total	Number of official Written concern	Total number Alert + Unofficial Written concern
Apr-19	29	698	12982	22224
May-19	94	716	11064	22650
Jun-19	78	671	10603	22732
Jul-19	104	661	9111	25017
Aug-19	86	580	8137	24410
Sep-19	124	553	6595	23237
Oct-19	91	446	6793	23811
Nov-19	72	471	6363	23311
Dec-19	27	425	6691	25021
Jan-20	42	643	5825	22466
Feb-20	38	698	6232	23945
Mar-20	58	994	6133	22692

Related Topics