Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am working on two tables that represents the list of orders and another table that represents the extra features customers ordered on each of those transactions. I can think of using multiple ApplyMaps, but is there any better way to do this?
Table A looks like this:
OrderId | VehicleId | BasePrice |
101 | 1 | 10,000 |
102 | 2 | 20,000 |
103 | 3 | 30,000 |
104 | 4 | 40,000 |
105 | 5 | 50,000 |
Table B looks like this:
OrderID | ExtraFeature | ExtraFees |
101 | Paint | 500 |
101 | FloorMat | 100 |
102 | Paint | 350 |
102 | Exhaust | 700 |
103 | Navigation | 1,500 |
103 | FloorMat | 200 |
103 | Paint | 600 |
104 | Wheels | 2,000 |
104 | Paint | 750 |
104 | Lights | 650 |
105 | Paint | 750 |
How do I get the final table to look like this:
OrderId | VehicleId | BasePrice | Paint | FloorMat | Exhaust | Navigation | Wheels | Lights |
101 | 1 | 10,000 | 500 | 100 | - | - | - | - |
102 | 2 | 20,000 | 350 | - | 700 | - | - | - |
103 | 3 | 30,000 | 600 | 200 | - | 1,500 | - | - |
104 | 4 | 40,000 | 750 | - | - | - | 2,000 | 650 |
105 | 5 | 50,000 | 750 | - | - | - | - | - |
Check out "generic load". there's a post solving this requirement:
The Generic Load - Qlik Community - 1473470
it will unpivot ExtraFeature, by default each distinct ExtraFeature will go into its own table, which on its own will give you what you want. But less efficient on memory but cleaner is to match your single larger table you can also add a for loop + join. I like to work from highest table number backwards instead of the article, this is what i would do.
tableB:
generic load
OrderID, ExtraFeature, ExtraFees
;
tableB:
load distinct * inline [
OrderID, ExtraFeature, ExtraFees
101, Paint, 500
101, FloorMat, 100
102, Paint, 350
102, Exhaust, 700
103, Navigation, 1500
103, FloorMat, 200
103, Paint, 600
104, Wheels, 2000
104, Paint, 750
104, Lights, 650
105, Paint, 750
]
;
let vNumberOfTables = NoOfTables();
For vTableNo = 0 to $(vNumberOfTables)
Let vTableNo_new = $(vNumberOfTables)-$(vTableNo);
Let vTableName = TableName($(vTableNo_new)) ;
Trace WORKING ON TABLE $(vTableNo_new);
If Subfield(vTableName,'.',1)='tableB' Then
Trace CREATED IN GENERIC LOAD. JOIN AND DROP;
Left Join ('tableA')
Load *
Resident [$(vTableName)];
Drop Table [$(vTableName)];
End If
Next vTableNo
SET vNumberOfTables =;
Check out "generic load". there's a post solving this requirement:
The Generic Load - Qlik Community - 1473470
it will unpivot ExtraFeature, by default each distinct ExtraFeature will go into its own table, which on its own will give you what you want. But less efficient on memory but cleaner is to match your single larger table you can also add a for loop + join. I like to work from highest table number backwards instead of the article, this is what i would do.
tableB:
generic load
OrderID, ExtraFeature, ExtraFees
;
tableB:
load distinct * inline [
OrderID, ExtraFeature, ExtraFees
101, Paint, 500
101, FloorMat, 100
102, Paint, 350
102, Exhaust, 700
103, Navigation, 1500
103, FloorMat, 200
103, Paint, 600
104, Wheels, 2000
104, Paint, 750
104, Lights, 650
105, Paint, 750
]
;
let vNumberOfTables = NoOfTables();
For vTableNo = 0 to $(vNumberOfTables)
Let vTableNo_new = $(vNumberOfTables)-$(vTableNo);
Let vTableName = TableName($(vTableNo_new)) ;
Trace WORKING ON TABLE $(vTableNo_new);
If Subfield(vTableName,'.',1)='tableB' Then
Trace CREATED IN GENERIC LOAD. JOIN AND DROP;
Left Join ('tableA')
Load *
Resident [$(vTableName)];
Drop Table [$(vTableName)];
End If
Next vTableNo
SET vNumberOfTables =;
thank you! this is exactly what i need!
Great! Please mark the thread complete if this has answered your question.