topic Re: Loading Unstructured Data from Excel in QlikView

Loading Unstructured Data from Excel

Vinothishere — Thu, 07 May 2015 20:28:45 GMT

Hi,

I have the sample data in the following format:

Dim1	Dim2	Qty
	ABC
	Stage1
100	A	3
101	B	4
	Stage2
101	A	8
102	D	9

I need to load this sample data in the following way:

Dim1	Dim2	Qty	Title1	Title2
100	A	3	ABC	Stage1
101	B	4	ABC	Stage1
101	A	8	ABC	Stage2
102	D	9	ABC	Stage2

Thanks in advance.

Re: Loading Unstructured Data from Excel

Ralf-Narfeldt — Fri, 08 May 2015 06:03:08 GMT

This preceding load should do it:

Load * where Len(Dim1)>0;

LOAD Dim1,

Dim2,

Qty

FROM

your_excel.xlsx

(ooxml, embedded labels, table is your_sheet_name);

Re: Loading Unstructured Data from Excel

Vinothishere — Fri, 08 May 2015 06:13:56 GMT

Its working,but how to populate the value for Title2 column?

Re: Loading Unstructured Data from Excel

jonathandienst — Fri, 08 May 2015 06:28:44 GMT

I would not call this unstructured, but multiline headers from Excel sheets can be awkward to use. Have a look at my post here Cross table for an example of how to do this.

Re: Loading Unstructured Data from Excel

Ralf-Narfeldt — Fri, 08 May 2015 06:41:54 GMT

Sorry, missed that you wanted to populate Title1 and Title2 as well.

Will Title1 repeat in the data?

Re: Loading Unstructured Data from Excel

Ralf-Narfeldt — Fri, 08 May 2015 07:41:06 GMT

So, this should do it with Title 1 hardcoded. If you have several similar files or Title1 changing, there would be some more code needed.

Source:

Temp:

Load *, IF(ISNULL(Temp) OR TRIM(Temp)='', Peek(Title2), Temp) as Title2;

Load *, If(IsNull(Dim1),Dim2,Null()) As Temp resident Source;

Result:

Load *, 'ABC' As Title1 resident Temp where Len(Dim1)>0;

Drop tables Source, Temp;

Re: Loading Unstructured Data from Excel

Vinothishere — Mon, 11 May 2015 13:58:41 GMT

Can you please explain the logic behind how the two load statements in Temp table works?

Re: Loading Unstructured Data from Excel

Ralf-Narfeldt — Mon, 11 May 2015 14:11:27 GMT

Temp:

//If Temp is Null or empty, that is, it is a value row

//get the previous value of Title2, which should be a Stage,

//otherwise (if it's a Stage row) put Temp there.

Load *, IF(ISNULL(Temp) OR TRIM(Temp)='', Peek(Title2), Temp) as Title2;

//Load all values from source plus a Temp field.

//If Dim1 is empty (Null) put Dim2, otherwise put Null

//That way each row with StageX gets this value,

//all value rows will have Null.

Load *, If(IsNull(Dim1),Dim2,Null()) As Temp resident Source;

//Remember that the bottom Load loads before the first one.

//Now we do the final load where we discard the non-value rows with the where //condition, adding the Title1.

Result:

Load *, 'ABC' As Title1 resident Temp where Len(Dim1)>0;

Please mark as Helpful/Answered if you are happy with this!