Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to ingest an Excel sheet into QlikView, but I have found that one field/column ends up having some cells/instances with multiple internal cell-lines of data. I need to effectively form some kind of loop to run through each of those internal cell-rows to pull out data.
To complicate it further, the data that is in each of those rows is actually a set of 4 separate fields all seemingly crammed into one cell.
Example (I believe data in the “Details” column is tab delineated internally because if, in Excel, I copy the contents and then paste into a new sheet, it pastes into the separate columns and rows):
I prettied this example up so you can get the feel of the cell-internal separate fields.
Item | Details | Type | Code |
Apple | Name Color Weight Price Gala Red 1 0.25 Fuji Red 1 0.30 Granny Green 1.1 0.35 | Fruit | 1111 |
Orange | Name Color Weight Price Florida Orange 1.5 0.86 Indian River Orange 1.7 0.92 | Fruit | 2222 |
What I want to get ultimately is an output like this (and I am okay with the title line being included if I have to):
Item | Details-Name | Details-Color | Details-Weight | Details-Price | Type | Code |
---|---|---|---|---|---|---|
Apple | Gala | Red | 1 | 0.25 | Fruit | 1111 |
Apple | Fuji | Red | 1 | 0.3 | Fruit | 1111 |
Apple | Granny | Green | 1.1 | 0.35 | Fruit | 1111 |
Orange | Florida | Orange | 1.5 | 0.86 | Fruit | 2222 |
Orange | California | Orange | 1.7 | 0.92 | Fruit | 2222 |
Anyone have a great ideas?
Can you check this?
Hello,
You can try something like this:
Data:
LOAD Item,
SubField(Details, Chr(10), 1) & ' Item' & Chr(10) &
Replace(Right(Details, Len(Details) - Index(Details, Chr(10))), Chr(10),
' ' & Item & Chr(10)) & ' ' & Item As Details,
Type,
Code
FROM [Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
FinalData:
LOAD Item,
Name As [Details-Name],
Color As [Details-Color],
Weight As [Details-Weight],
Price As [Details-Price]
FROM_FIELD (Data, Details)
(txt, utf8, embedded labels, delimiter is spaces, msq);
Left Join
Load Item,
Type,
Code
Resident Data;
Drop Table Data;