topic Re: What functions in data load script should I use on these two tables? for Vehicle sales transactions in App Development

What functions in data load script should I use on these two tables? for Vehicle sales transactions

jeff_k — Sat, 07 Aug 2021 13:27:52 GMT

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

Re: What functions in data load script should I use on these two tables? for Vehicle sales transactions

stevejoyce — Sat, 07 Aug 2021 15:25:49 GMT

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 =;

Re: What functions in data load script should I use on these two tables? for Vehicle sales transactions

jeff_k — Mon, 09 Aug 2021 09:56:30 GMT

thank you! this is exactly what i need!

Re: What functions in data load script should I use on these two tables? for Vehicle sales transactions

stevejoyce — Mon, 09 Aug 2021 10:31:25 GMT

Great! Please mark the thread complete if this has answered your question.