Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Mahamed.
This questions seems to be problematic due the nature of the data source.
I'm not quite sure if it's possible to make it look like the desired output in the tab2, on a clean way.
SubField() is the function you're looking for, of course. It splits strings into new rows, based on the delimiter you want to split into.
If you select from letters A, B, C, D, E... using "parameter (ooxml, no labels, header is 2 lines, table is Data);". You can solve the problem of selecting difficult fields, like the columns of Period, which are split into Quarter and Year.
The main problem comes when you split into new rows.
In the formulas of the script, we ask field D to split into rows for each break line or carriage. But how can we know that Q1 belongs to Year 2022?
I don't see a natural way to link Q1 and 2022, and Q2 to 2023, therefore, the result of the image happens.
Let's see if someone else comes with a better idea.
PD: Let's see it from other point of view. We should load rows, telling Qlik that the delimiter is a break line.
If it was a CSV, it would be easier to do this, but Qlik can't change the field delimiter while loading a Excel file, so we have to do it for each field manually, I'll leave you this code:
Terrible workaround:
OriginalTable:
LOAD A,
B,
C,
D,
E,
F,
RecNo() as ID
FROM
[changePath\IssueFile.xlsx]
(ooxml, no labels, header is 2 lines, table is Data);
Description:
LOAD
RowNo() as ID_Description,
SubField(C, CHR(10)) as Description
RESIDENT OriginalTable;
Quarter:
LOAD
RowNo() as ID_Quarter,
SubField(D, CHR(10)) as Quarter
RESIDENT OriginalTable;
Year:
LOAD
RowNo() as ID_Year,
SubField(E, CHR(10)) as Year
RESIDENT OriginalTable;
ConditionalTerms:
LOAD
RowNo() as ID_ConditionalTerm,
SubField(F, CHR(10)) as ConditionalTerms
RESIDENT OriginalTable;
FinalTable:
LOAD ID_Description as ID_ROW,
Description
RESIDENT Description;
LEFT JOIN (FinalTable)
LOAD ID_Quarter as ID_ROW,
Quarter
RESIDENT Quarter;
LEFT JOIN (FinalTable)
LOAD ID_Year as ID_ROW,
Year
RESIDENT Year;
LEFT JOIN (FinalTable)
LOAD ID_ConditionalTerm as ID_ROW,
ConditionalTerms
RESIDENT ConditionalTerms;
LEFT JOIN
LOAD A as Type,
B as Area,
SubField(C, Chr(10)) as Description
RESIDENT OriginalTable;
DROP TABLES OriginalTable, Description, Quarter, Year, ConditionalTerms;
This will result in the desired output. This is a terrible workaround for the example you submitted, and might not be the solution for a bigger data set.
As I said before, someone else might have an optimized and easier solution to the problem.
Regards,
