topic Re: Crosstable loading of data from excel in App Development

Crosstable loading of data from excel

Aspiring_Developer — Tue, 11 Feb 2020 05:38:03 GMT

Hi Team,

I want to load the data from excel using crosstable for the attached file:-

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

Re: Crosstable loading of data from excel

petter — Tue, 11 Feb 2020 07:04:41 GMT

Is the table you show directly in your post how you want the data to be in Qlik Sense after doing a load from the Excel file?

If so - is it how you want it to be in a data table in the data model or as a UI table in a sheet?

Re: Crosstable loading of data from excel

Aspiring_Developer — Tue, 11 Feb 2020 08:45:04 GMT

I want the output like this:-

	1 months data
	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	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!

Re: Crosstable loading of data from excel

petter — Tue, 11 Feb 2020 07:13:11 GMT

What is PPM and IPB?

Re: Crosstable loading of data from excel

Aspiring_Developer — Tue, 11 Feb 2020 08:47:52 GMT

Hi,

Please find the updated output above

I just want it like this

	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	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!

Re: Crosstable loading of data from excel

petter — Tue, 11 Feb 2020 09:02:50 GMT

This should work for you:

FOR Each vMonth IN 'April','May','June','July','August','September','October','November','December','January' DATE_AND_ROW_DATA: FIRST 2 LOAD RowNo() AS R#, If(IsNum("VLS Global"),Date("VLS Global"),Peek(Date)) AS Date, F14 AS CustomerPPM, F15 AS SitePPM, F17 AS CustomerIPB, F18 AS SiteIPB FROM [lib://Downloads/ZZ Global January 2019.xlsx] (ooxml, embedded labels, table is [Quality Performance-$(vMonth)]) WHERE "VLS Global" = 'TOTAL ACTUAL' OR RecNo()=2; DATA: LOAD Date(Date,'MMM.YY') AS Date,CustomerPPM,SitePPM,CustomerIPB,SiteIPB RESIDENT DATE_AND_ROW_DATA WHERE RecNo()=2; DROP TABLE DATE_AND_ROW_DATA; NEXT vMonth=;

It seems there is something odd about the June sheet though - you'll have to figure that out yourself ... The code works as such.

I must caution you that reading from a complex spreadsheet sometimes makes me feel like doing open heart surgery in a sailboat in open waters...

Please click the like button or heck if the response was correct you could even mark it as a correct solution.
Believe me - it will be greatly appriciated by the contributors ...
We love likes as much as anyone posting anything on social media

Re: Crosstable loading of data from excel

Aspiring_Developer — Tue, 11 Feb 2020 09:15:38 GMT

I was trying to create the croostable for this excel tab "Quality Perfo Dashboard-Yearly " in the attached sheet. Will it work for that also as it has different dimensions and strucuture like below:-

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

And i wish to see the output like

Customers , Month, NOK parts in customer database - total, Real NOK parts manufactured - total

Brazil , April-19 3, 34

Note: please refer the attached excel in the post and look for "Quality Perfo Dashboard-Yearly " excel tab

Re: Crosstable loading of data from excel

petter — Tue, 11 Feb 2020 09:22:52 GMT

I actually answered your question and problem.

Please be polite and give me credit for that.

Yes - of course it can be done. The example I gave you should give you the pointers to learn and solve it on your own. It is all about data wrangling and transforming the raw data into a shape and form that can serve analysis and visulization in Qlik.

Re: Crosstable loading of data from excel

Aspiring_Developer — Tue, 11 Feb 2020 09:38:07 GMT

Okay, i will try. Thank you for the support 😊

Re: Crosstable loading of data from excel

petter — Tue, 11 Feb 2020 09:47:26 GMT

Re: Crosstable loading of data from excel

Aspiring_Developer — Tue, 11 Feb 2020 10:36:12 GMT

Hi,

I am trying to do it for the other tab "Quality Perfo Dashboard-Yearly "

Earlier we had "April19" under VLS Global and we created date and also selected few columns. But now here we do not have date instaed we have countries and also i have to select all the data like "NOK parts in customer databas total" under "April-19" till the end "March-20". Can you please suggest something ? So far i had tried fetching 2 rows and loading VLS global and other fields just like we did earlier but i am not able proceed. A little hint can save me . Kinldy suggest.

VLS Global		s

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

Re: Crosstable loading of data from excel

Aspiring_Developer — Tue, 11 Feb 2020 11:22:24 GMT

Can anyone please help me on this ?