Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
colinodonnel
Creator II
Creator II

Creating a new column in the script

Hello there,

I currently have a table like the following:

 

ProductLetterValues
MilkA1
MilkB2
MilkC3
ButterD4
ButterA4
ButterB5
CheeseC6
CheeseD6

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.

ProductABCD
Milk123
Butter45 4
Beer
Cheese 66

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:

 

ProductLetterValuesABCD
MilkA11
MilkB20
MilkC30
ButterD40
ButterA44
ButterB50
CheeseC60
CheeseD60
MilkA10
MilkB2 2
MilkC3 0
ButterD4 0
ButterA4 0
ButterB5 5
CheeseC6 0
CheeseD6 0

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

Help!

Thanks,

Colin

1 Solution

Accepted Solutions
jaumecf23
Creator III
Creator III

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;

View solution in original post

8 Replies
jaumecf23
Creator III
Creator III

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
Partner - Master II
Partner - Master II

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
Partner - Master II
Partner - Master II

Example here

zebhashmi
Specialist
Specialist

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

colinodonnel
Creator II
Creator II
Author

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 KeyProductLetterValues
11MilkA1
21MilkB2
31MilkC3
42ButterD4
52ButterA4
62ButterB5
73CheeseC6
83CheeseD6

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 KeyProductABCD
1Milk123
2Butter45 4
3Beer
4Cheese 66

Thank you very much!!!

Colin

colinodonnel
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

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