Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Naule
Contributor II
Contributor II

Column label of value based on other column

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: 

ZendingAdresDatumTypeWaardeTypeWaardeTypeWaardeTypeWaardeTypeWaarde
14397 Arlington Avenue30-8-2019FRT48,95FS4,90BTW11,31    
24201 Heritage Road6-9-2019FRT47,30FS4,73ADH8,00BTW12,61  
31953 Coffman Alley30-8-2019FRT29,15FS2,92ZWR25,00MAX25,00BTW17,23
42300 Huntz Lane2-9-2019FRT29,70FS2,97GRT10,00BTW8,96  
52547 Jadewood Drive2-9-2019FRT27,50FS2,75BTW6,35    
63425 Irving Road4-9-2019FRT33,55FS3,36BTW7,75    
74470 Margaret Street1-9-2019FRT33,00FS3,30BTW7,62    
84593 Conaway Street4-9-2019FRT42,35FS4,24BTW9,78    
94556 Maloy Court12-9-2019FRT51,70FS5,17BTW11,94    
102887 Pinnickinick Street1-9-2019VAT530,25DUT50,50VSP34,85    

To:

ZendingAdresDatumFRTFSBTWADHZWRMAXGRTVATDUTVSP
14397 Arlington Avenue30-8-201948,954,9011,31       
24201 Heritage Road6-9-201947,304,7312,618,00      
31953 Coffman Alley30-8-201929,152,9217,23 25,0025,00    
42300 Huntz Lane2-9-201929,702,978,96   10,00   
52547 Jadewood Drive2-9-201927,502,756,35       
63425 Irving Road4-9-201933,553,367,75       
74470 Margaret Street1-9-201933,003,307,62       
84593 Conaway Street4-9-201942,354,249,78       
94556 Maloy Court12-9-201951,705,1711,94       
102887 Pinnickinick Street1-9-2019       530,2550,5034,85
2 Solutions

Accepted Solutions
Frank_Hartmann
Master II
Master II

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

clipboard_image_0.png

hope this helps

View solution in original post

Frank_Hartmann
Master II
Master II

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

View solution in original post

6 Replies
maxronny
Contributor III
Contributor III

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)

ZendingAdresDatumTypeADHBTWDUTFRTFSGRTMAXVATVSPZWR
14397 Arlington Avenue30/08/201943707,00-11,31-48,954,90-----
24201 Heritage Road06/09/201943714,008,0012,61-47,304,73-----
31953 Coffman Alley30/08/201943707,00-17,23-29,152,92-25,00--25,00
42300 Huntz Lane02/09/201943710,00-8,96-29,702,9710,00----
52547 Jadewood Drive02/09/201943710,00-6,35-27,502,75-----
63425 Irving Road04/09/201943712,00-7,75-33,553,36-----
74470 Margaret Street01/09/201943709,00-7,62-33,003,30-----
84593 Conaway Street04/09/201943712,00-9,78-42,354,24-----
94556 Maloy Court12/09/201943720,00-11,94-51,705,17-----
102887 Pinnickinick Street01/09/201943709,00--50,50----530,2534,85-
Frank_Hartmann
Master II
Master II

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

clipboard_image_0.png

hope this helps

Naule
Contributor II
Contributor II
Author

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

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

Frank_Hartmann
Master II
Master II

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

maxronny
Contributor III
Contributor III

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.