Creating a new column in the script

[colinodonnel]

Hello there,

I currently have a table like the following:

Product	Letter	Values
Milk	A	1
Milk	B	2
Milk	C	3
Butter	D	4
Butter	A	4
Butter	B	5
Cheese	C	6
Cheese	D	6

I want to convert it to the following structure from the script,

namely move the repeating values in the Letter column to 4 individual columns and transfer the values according.

This would also mean the Letter column is removed.

Product	A	B	C	D
Milk	1	2	3
Butter	4	5		4
Beer
Cheese			6	6

I have tried both the Generic Load (with an additional script to consolidate the generic tables taken from here Sample Generic Load Script) but what is happening is that extra rows are being generated as follows:

Product	Letter	Values	A	B	C	D
Milk	A	1	1
Milk	B	2	0
Milk	C	3	0
Butter	D	4	0
Butter	A	4	4
Butter	B	5	0
Cheese	C	6	0
Cheese	D	6	0
Milk	A	1	0
Milk	B	2		2
Milk	C	3		0
Butter	D	4		0
Butter	A	4		0
Butter	B	5		5
Cheese	C	6		0
Cheese	D	6		0

I have also tried using If statements and calculated fields instead of a Generic Load but with the same effect.

Help!

Thanks,

Colin

[jaumecf23 · Accepted solution]

Hi,

I think that the following code will be helpfull fot you:

Temp:

LOAD * INLINE [

Product,Letter,Values

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

    LET vTable=TableName($(i));

    IF WildMatch('$(vTable)', 'GenericTable.*') THEN

       LEFT JOIN (Temp_2) LOAD * RESIDENT [$(vTable)];

       DROP TABLE [$(vTable)];

    ENDIF

NEXT

DROP Table Temp;

[ogster1974]

wouldn't it be better to keep the structure as is and just create a pivot table of your data in the front end?

[ogster1974]

Example here

[zebhashmi]

it would be Cross Table in Qlik i think you can't do that.

[colinodonnel]

Hi Jaume,

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!!!

Colin

[colinodonnel]

Hi Andy,

Thank you for the reply.

Yes that would works well and i understand is preferable from an efficiency point of view as described here:

The Generic Load

However, there are two reasons why I wanted to consolidate the table and view in Excel.

Number 1

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:

Let vToday=Date(Today(1),'DD-MMM-YYY');

Store * from OutputTableName into [lib://......./]OutputTableName_$(vToday).CSV (txt);

Number 2:

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.

Best Regards,

Colin

[colinodonnel]

Hi Jahanzeb,

Thanks for the reply.

I think a Cross table works in the opposite direction, namely columns to rows.

The Crosstable Load

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:

http://help.qlik.com/en-US/sense/2.1/Subsystems/Hub/Content/LoadData/how-to-prepare-excel-files-for-...

Cheers,

Colin

[zebhashmi]

Thank you! I did not know that we can have crosstable in Qlik as well.