Simplifying a table structure

[dan205]

I have a spreadsheet to load into Qlik in the following format:

Order ID	Org1	OrgType1	Amount1	Org2	OrgType2	Amount2	Org3	OrgType3	Amount3
10001	AAA	Internal	1000	BBB	Internal	300	CCC	External	600
10002	DDD	Internal	1500	AAA	Internal	200	EEE	External	900
10003	EEE	External	800	FFF	Internal	700	AAA	Internal	1200

...and so on.

But what I really want to work with in Qlik is a table like this:

Order ID	Org	OrgType	Amount
10001	AAA	Internal	1000
10001	BBB	Internal	300
10001	CCC	External	600
10002	DDD	Internal	1500
10002	AAA	Internal	200
10002	EEE	External	900
10003	EEE	External	800
10003	FFF	Internal	700
10003	AAA	Internal	1200

Can anyone tell me how to get my data into that format?

Thanks in advance,
Dan

[henrikalmen · Accepted solution]

So you have an excel file with multiple columns. The first column is OrderID, and the rest of the columns are in groups of three with an index number in the field name.

I suggest using a variable for the index number that you have, and iterate your load over that.

//define columns in an empty table
MyTable: load [Order ID], Org, OrgType, Amount autogenerate(0);

//iterate over the number of different coliumn groupings
FOR i = 1 to 5 //if 5 is your last index number
  concatenate(MyTable)
  LOAD
    [Order ID],
    [Org$(i)] as Org,
    [OrgType$(i)] as OrgType,
    [Amount$(i)] as Amount
  FROM [excelfile] (...) //this row must be changed for your actual file's properties
  ;
NEXT i

[dan205]

Thanks, I did something similar with concatenate but without the loop.