Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings,
I have a situation where I need to load a table and provide a column with the number of Rev-Trac# values found for each Rev-Trac# value in the table.
Example with count result (TX_Count):
Rev-Trac # TX_Version TX_Transport TX_Sequece TX_Count
1111 2 ABCK901222 10 3
1111 3 ABCK901225 20 3
1111 4 ABCK901239 30 3
2222 2 ABCK901255 10 1
3333 2 ABCK901251 20 2
3333 3 ABCK901249 10 2
Here is my scripting to load the table into memory...
TRANSPORT_DATA_INT:
LOAD
Num
(@1) as [Rev-Trac#],, 'Request')))
));
I have attempted adding an addition load field of "Count(@1) as TX_COUNT and adding the group by statement after the loaction of the file, but have not had much luck. Does anyone know what I need to do and what the proper script should be to get the Rev-Trac# counts?
Try adding this to your load script:
LEFT JOIN (TRANSPORT_DATA_INT)
LOAD [Rev-Trac#],
Count([TX_Version]) AS [TX_Count]
RESIDENT TRANSPORT_DATA_INT GROUP BY [Rev-Trac#];
Try adding this to your load script:
LEFT JOIN (TRANSPORT_DATA_INT)
LOAD [Rev-Trac#],
Count([TX_Version]) AS [TX_Count]
RESIDENT TRANSPORT_DATA_INT GROUP BY [Rev-Trac#];
Worked like a charm! Thanks for the help swuehl.
So that I understand... One would use the LEFT JOIN to ADD another column to an existing table. Then when instructing the join where it is getting it's data from I have to use the RESIDENT call to call the already exisitng table. Does that sound about right???
Yes, I think that's correct.
Instead of the resident table, you can get the data from the original input source or a qvd, of course.