Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.
The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2
while Doc2 has Sheet1/Sheet2/Sheet3 etc.
I need to create a table of Doc name and Sheets belonging to that Doc.
Then I want to load the sheets from their associated docs in a loop statement.
Anyone any ideas?
Thanks
Sir, plz refrsh the page
Can you paste your loading script so that I can check where it is creating the synthetic key?
If you can attach the excel files, it would have been easier to fix the issue
Sir, here's my script & the excel is also attached, now i think u can solve the problems of Join crosstable & load across sheets
Regards
Can you explain why you are loading two crosstables from same sheet and then join it together? What is the purpose?
Sir,
First of all tab shud be Crosstable(A,X,9) instead of Crosstable(A,X,7)
as u can see from the two crosstables i need last 4 cols to be clubbed as A, their data as X
in Tab2: U & UA are to be clubbed into C & their values in D
Am i doing anything wrong, is their a simpler solution
REGARDS
Thanks for the clarification, Please add the below script to append the two tables together.
Final_Table:
LOAD *, '1' as Temp Resident Tab1;
Concatenate
LOAD *, '2' as Temp Resident Tab2;
DROP Table Tab1;
DROP Table Tab2;
If you need an inner join then use the below script
Final_Table:
LOAD *, '1' as Temp Resident Tab1;
inner Join LOAD * Resident Tab2;
DROP Table Tab1;
DROP Table Tab2;
The point is - you need to create a dummy field in the final table - i.e. '1' as Temp
Hope this is what you wanted to achieve
Regards,
Sajeevan
Thanks a lot Sir,it did what i needed. No synthetic key as well.
But Sir, i'm not able to understand the role of the dummy field.Could u throw some light on it.
Also Sir, when in future the data is added for Feb, Mar etc. then would it be ok to simply schedule the reload thru Windows Scheduler or there are any changes reqd. in the script as well.
Thanks
Hi Sanjeevan,
i have one question regarding the cross table, if in a excel if i have three different sheet (exp: Australia, Janpan, China) and the sheets contain same fields but different values and along with the values i also want to upload the sheet name as a separete filed (in the cross table '$(sheet)' as country is not working) can you
i think u shud remove the quotes (' ') from $(sheet)
Regards
Can you please send your load script to check where the change in load script needed?