topic Important data before header in QlikView

Important data before header

Wed, 25 Nov 2020 16:16:04 GMT

Hello QV community - quick question: I have a spreadsheet where the first row contains important data, the second row contains the headers, and then the data begins on row 3. Here's an example:

Country: USA

State^County^Sales

NY^Nassau^123456

NJ^Passaic^234567

OR^Multnomah^345678

I want to apply the country USA to every row. Is there a way to do this in the load script?

Thank you

Re: Important data before header

Gysbert_Wassenaar — Fri, 18 Mar 2016 15:53:43 GMT

Temp:

First 1

Load subfield(@1,':' -1) as Country From MyExcelFile (ooxml, no labels, table is Sheet1);

LET vCountry = peek('Country');

Drop Table Temp;

Data:

LOAD

'$(vCountr)' as Country,

Subfield(@1, '^', 1) as State,

Subfield(@1, '^', 2) as County,

Subfield(@1, '^', 3) as Sales

FROM MyExcel File (ooxml, header is 3 lines, no labels, table is Sheet1);