Columns and data are missing when loading with 'No Label'Article Number: 000036813 | Last Modified: 2019/01/18
Below 'text file' is loaded with no labels. In the 'text' file, the first line has less columns than the second line.
LOAD * Inline [ BENELUX; Belgium; Netherlands DZ; AO; SH; BJ; BW; BF; BI; CM; CV; CF; DJ; EG ](txt, no labels, delimiter is ';');
But after loading complete, the table only has the columns created based on the first line. Other columns in the second line are ignored. Hence the related data get lost as well. There is no warning or error message get generated. This can be replicated in both QlikView and Qlik Sense.
When loading data with no label, the columns are designed to be always created based on the first record in Inline table. Additional workaround provided here. Basically the workaround is to calculate the maximum columns the longest row contains in the text file, then loop to create all columns based on the maximum column amount.
// Identify no of columns in longest row MaxNoOfColumns: LOAD Max(SubStringCount(@1, ';')) AS NoOfColumns Inline [ BENELUX; Belgium; Netherlands DZ; AO; SH; BJ; BW; BF; BI; CM; CV; CF; DJ; EG ](txt, utf8, no labels, delimiter is \x3, no quotes); // Concatenate max number of columsn into field definition string LET vFields = '@1'; FOR i=2 TO Peek('NoOfColumns',0,'MaxNoOfColumns') LET vFields = '$(vFields),@$i'; NEXT // Load text date from text file based on genrated field definition LOAD $(vFields) Inline [ BENELUX; Belgium; Netherlands DZ; AO; SH; BJ; BW; BF; BI; CM; CV; CF; DJ; EG ](txt, utf8, no labels, delimiter is ';', no quotes);
Have a Question?
Search Qlik's Support Knowledge database or request assisted support for highly complex issues.Submit a case
Experiencing a serious issue, please contact us by phone. View phone numbers and hours by region.