topic Loading/Transposing data in row format from text file in QlikView

Loading/Transposing data in row format from text file

sbfernandes — Sat, 16 Nov 2024 17:47:09 GMT

Greetings

I am trying to load data from a text file. The Headers are in rows while data is in columns.

Below is the data in text file (also attached).

----------------------------FILE START--------------------------------------------------------------------------

SEQ | 1 | 2 | 3 |
Name | John | Steven | Mark |
Family Name | Happy | Glad | Sun |
ID | 111 | 222 | 333 |
| | | |
Gender | Male | Male | Male |
DOB | 020110 | 150211 | 221113 |

SEQ | 7 | 8 | 9 |
Name | mary | Hope | Lisa |
Family Name | Wilkins | Gladys | Sinter |
ID | 119 | 229 | 339 |
| | | |
Gender | FeMale | FeMale | FeMale |
DOB | 020110 | 150211 | 221113 |

SEQ | 10 |
Name | Susan |
Family Name | Marlb |
ID | 449 |
| |
Gender | FeMale |
DOB | 120114 |

--------------------------------------------------------FILE END------------------------------------------------------

Required table should look like

SEQ	Name	Family Name	ID	Gender	DOB
1	John	Happy	111	Male	020110
2	Steven	Glad	222	Male	150211
3	Mark	Sun	333	Male	221113
7	mary	Wilkins	119	FeMale	020117
8	Hope	Gladys	229	FeMale	150218
9	Lisa	Sinter	339	FeMale	221119
10	Susan	Marlb	449	FeMale	120114

Re: Loading/Transposing data in row format from text file

chrismarlow — Tue, 24 Nov 2020 13:37:15 GMT

Hi,

Here is a start;

data: LOAD If(RowNo()=1,1,If(@1='SEQ',Peek(RC)+1,Peek(RC))) AS RC, *; LOAD @1, @2, @3, @4 FROM SampleInputFile.txt (txt, codepage is 28591, no labels, delimiter is '|', msq); seqmap: CrossTable (TextField,SEQ,1) Load RC, @2, @3, @4 RESIDENT data WHERE @1='SEQ'; name: CrossTable (TextField,Name,1) Load RC, @2, @3, @4 RESIDENT data WHERE @1='Name'; Left Join (seqmap) LOAD * Resident name; DROP TABLES data, name;

You should be able to make copies & amend the code from 'name:' for each of your fields & you might need to add more to the DROP TABLES statement & possibly tidy up (you get some empty rows where you don't have 3 SEQs in a row).

Cheers,

Chris.

Re: Loading/Transposing data in row format from text file

sbfernandes — Wed, 25 Nov 2020 05:40:31 GMT

Hi Chrismarlow

Thanks for the suggestion. This would work for the sample file. However my actual data contains around 50 column headers (in rows) and 10's of thousands of records.