topic Re: Column label of value based on other column in QlikView

Column label of value based on other column

Naule — Wed, 25 Nov 2020 16:16:04 GMT

I have got a CSV, which in toned down form looks something along the lines of below. It holds various costs types in a column and then has got the value for that type in the subsequent column.

I would like to read this in as a table with all the types as column headers. Illustrative tables below and test CSV attached.

Thanks in advance, Grtz N.

------------------------ Illustrative tables example:
Going from:

Zending	Adres	Datum	Type	Waarde	Type	Waarde	Type	Waarde	Type	Waarde	Type	Waarde
1	4397 Arlington Avenue	30-8-2019	FRT	48,95	FS	4,90	BTW	11,31
2	4201 Heritage Road	6-9-2019	FRT	47,30	FS	4,73	ADH	8,00	BTW	12,61
3	1953 Coffman Alley	30-8-2019	FRT	29,15	FS	2,92	ZWR	25,00	MAX	25,00	BTW	17,23
4	2300 Huntz Lane	2-9-2019	FRT	29,70	FS	2,97	GRT	10,00	BTW	8,96
5	2547 Jadewood Drive	2-9-2019	FRT	27,50	FS	2,75	BTW	6,35
6	3425 Irving Road	4-9-2019	FRT	33,55	FS	3,36	BTW	7,75
7	4470 Margaret Street	1-9-2019	FRT	33,00	FS	3,30	BTW	7,62
8	4593 Conaway Street	4-9-2019	FRT	42,35	FS	4,24	BTW	9,78
9	4556 Maloy Court	12-9-2019	FRT	51,70	FS	5,17	BTW	11,94
10	2887 Pinnickinick Street	1-9-2019	VAT	530,25	DUT	50,50	VSP	34,85

To:

Zending	Adres	Datum	FRT	FS	BTW	ADH	ZWR	MAX	GRT	VAT	DUT	VSP
1	4397 Arlington Avenue	30-8-2019	48,95	4,90	11,31
2	4201 Heritage Road	6-9-2019	47,30	4,73	12,61	8,00
3	1953 Coffman Alley	30-8-2019	29,15	2,92	17,23		25,00	25,00
4	2300 Huntz Lane	2-9-2019	29,70	2,97	8,96				10,00
5	2547 Jadewood Drive	2-9-2019	27,50	2,75	6,35
6	3425 Irving Road	4-9-2019	33,55	3,36	7,75
7	4470 Margaret Street	1-9-2019	33,00	3,30	7,62
8	4593 Conaway Street	4-9-2019	42,35	4,24	9,78
9	4556 Maloy Court	12-9-2019	51,70	5,17	11,94
10	2887 Pinnickinick Street	1-9-2019								530,25	50,50	34,85

Re: Column label of value based on other column

maxronny — Fri, 13 Sep 2019 09:04:59 GMT

You've got to change the file csv import like the template attached.

if you do this, the qvw will report this pivot (see the file.qvw attached)

Zending	Adres	Datum	Type	ADH	BTW	DUT	FRT	FS	GRT	MAX	VAT	VSP	ZWR
1	4397 Arlington Avenue	30/08/2019	43707,00	-	11,31	-	48,95	4,90	-	-	-	-	-
2	4201 Heritage Road	06/09/2019	43714,00	8,00	12,61	-	47,30	4,73	-	-	-	-	-
3	1953 Coffman Alley	30/08/2019	43707,00	-	17,23	-	29,15	2,92	-	25,00	-	-	25,00
4	2300 Huntz Lane	02/09/2019	43710,00	-	8,96	-	29,70	2,97	10,00	-	-	-	-
5	2547 Jadewood Drive	02/09/2019	43710,00	-	6,35	-	27,50	2,75	-	-	-	-	-
6	3425 Irving Road	04/09/2019	43712,00	-	7,75	-	33,55	3,36	-	-	-	-	-
7	4470 Margaret Street	01/09/2019	43709,00	-	7,62	-	33,00	3,30	-	-	-	-	-
8	4593 Conaway Street	04/09/2019	43712,00	-	9,78	-	42,35	4,24	-	-	-	-	-
9	4556 Maloy Court	12/09/2019	43720,00	-	11,94	-	51,70	5,17	-	-	-	-	-
10	2887 Pinnickinick Street	01/09/2019	43709,00	-	-	50,50	-	-	-	-	530,25	34,85	-

Re: Column label of value based on other column

Frank_Hartmann — Fri, 13 Sep 2019 09:11:43 GMT

You dont need to change your csv, just load and concatenate your data like in attached qvw.

hope this helps

Re: Column label of value based on other column

Naule — Fri, 13 Sep 2019 09:19:49 GMT

Awesome, this is exactly what I was looking for (preferred not to change the CSV indeed).

Re: Column label of value based on other column

maxronny — Fri, 13 Sep 2019 09:54:29 GMT

ok i understand you,

but if you gonna have 40 or 50 different columns type...the concatenate function will be difficult in script edit...so i suggest you to change the CSV file import.

best regards

Re: Column label of value based on other column

Frank_Hartmann — Fri, 13 Sep 2019 10:20:18 GMT

if you use a simple for next loop this problem should be a very easy thing!

Re: Column label of value based on other column

maxronny — Fri, 13 Sep 2019 10:57:38 GMT

hi Frank, i apologize for my bad english...my last post to explain this:

1) as in the original post i read "It holds various costs types in a column and then has got the value for that type in the subsequent column

2) i consequently thinked the the cost types could be very large, for example 40 or more different cost types

3) so i suggest to the post to change the CSV file with the template i uploaded to avoid the concatenate function in the script editor.