I think that the following code will be helpfull fot you:
LOAD * INLINE [
Milk, A, 1
Milk, B, 2
Milk, C, 3
Butter, D, 4
Butter, A, 4
Butter, B, 5
Cheese, C, 6
Cheese, D, 6
Temp_2: Load distinct Product Resident Temp;
GenericTable: Generic LOAD Product, Letter, Values Resident Temp;
FOR i = NoOfTables()-1 to 0 STEP -1
IF WildMatch('$(vTable)', 'GenericTable.*') THEN
LEFT JOIN (Temp_2) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
DROP Table Temp;
Thank you for the response.
Yes that works within the context of my question.
However, as I am work and the information is confidential I could not reveal the full problem that I was working on.
The error was made earlier on in my data transformation and the word "distinct" in your code helped me solve it.
The issue was with an Autonumber Primary Key in a Preceding Load Block based on 12 columns.
AutoNumber(FieldName1 & FieldName2 & ) as "Primary Key"
I simply removed "Product" from this Autonumber.
Original Primary Key Revised Primary Key Product Letter Values 1 1 Milk A 1 2 1 Milk B 2 3 1 Milk C 3 4 2 Butter D 4 5 2 Butter A 4 6 2 Butter B 5 7 3 Cheese C 6 8 3 Cheese D 6
I then amended your code as follows:
Temp_2: Load distinct "Revised Primary Key", Product Resident Temp;
And this produced the required table structure:
Revised Primary Key Product A B C D 1 Milk 1 2 3 2 Butter 4 5 4 3 Beer 4 Cheese 6 6
Thank you very much!!!
Thank you for the reply.
Yes that would works well and i understand is preferable from an efficiency point of view as described here:
However, there are two reasons why I wanted to consolidate the table and view in Excel.
I am transforming an very messy excel spreadsheet and for compliance purpose, I need to keep a "transformed" version on file.
The Store function helps and
the Qlik Sense script has allowed me to automate its restructure which is great!
I use the following code to save the output locally:
Store * from OutputTableName into [lib://......./]OutputTableName_$(vToday).CSV (txt);
When viewing large datasheets quickly, I prefer Excel than the table viewer in Qlik Sense.
Whilst the Data Manager can do this and does provide additional summary information on the table, it must be reloaded after each change to the script and then I need to click the edit sheet button to see further.
Its a little bit fiddly.
Thanks for the reply.
I think a Cross table works in the opposite direction, namely columns to rows.
A Generic load does the opposite.
As I happens I did a Crosstable eariler in the load script to convert multiple date columns into one single date column as recommended here: