Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have date in the following way.
Product Time1 Time2 Emx Loc Value Tab1 Tab2 Tab3 Tab4 Col1 COl2 Col3
X 20-3-14 14-03-10 ABC 12 30 A B C DD 1 2 3
Y 20-3-14 14-03-10 ABC 12 40 D B C DD 7 4 5
Key can be formed on Product ,Time1 ,Time2 , Emx ,Loc.
Now I need Output with new Columns : Number, Tab. Col
Product Time1 Time2 Emx Loc Value Number Tab Col
X 20-3-14 14-03-10 ABC 12 30 1 A 1
X 20-3-14 14-03-10 ABC 12 30 2 B 2
X 20-3-14 14-03-10 ABC 12 30 3 C 3.
Y 20-3-14 14-03-10 ABC 12 40 1 D 7
Y 20-3-14 14-03-10 ABC 12 40 2 B 4
Y 20-3-14 14-03-10 ABC 12 40 3 C 5
So basically the values in Number field should be based on number of Tab's and COls. In my case I have three 3 Tabs and 3 Cols(Tab1, Tab2, Tab3) are there for all records so Number should be 1 2 3 .
Can anyone help me on this.
BR,
Neehu
I think this will do for you
Cross tables are table where facts are divided on the basis of dimension values.
for example we have sales for every month. then, if in our database we load sales amounts as a separated field for every month as
Product Jan Feb Mar etc
a 100 200 300
So, basically we have 1 single fact as sales amount here, and jan-dec are dimension memebers of month dimension but we are creating different fields for every dimension member and treating them as different field.
Loading data this way, makes it difficult to properly aggregrate the data over different dimensions and difficult to do analysis.
So, we convert this crosstable into a normal table.
using
crosstable (Month, Amount)
load
*
from a.qvd(qvd);
then we will get the same table as
Product Month Amount
a jan 100
a feb 200
a mar 300
I hope you understand now, what is crosstable. let me know if it solves your concern.
Regards,
Sujeet
Temp:
Load * Inline [
Product,Time1,Time2, Emx, Loc,Value, Tab1,Tab2,Tab3,Tab4,Col1,COl2,Col3
X,20-3-14,14-03-10,ABC, 12,30, A, B, C, DD,1, 2, 3
Y, 20-3-14,14-03-10,ABC, 12,40,D, B, C, DD,7, 4, 5 ];
Temp1:
Load Product,Time1,Time2, Emx, Loc,Value,Tab1 As Tab,Col1 As Col Resident Temp;
Concatenate
Load Product,Time1,Time2, Emx, Loc,Value,Tab2 As Tab,COl2 As Col Resident Temp;
Concatenate
Load Product,Time1,Time2, Emx, Loc,Value,Tab3 As Tab,Col3 As Col Resident Temp;
NoConcatenate
Load *,AutoNumber(RecNo(), Product&'_'&Time1&'_'&Time2&'_'& Emx&'_'& Loc&'_'&Value) As Number Resident Temp1;
Drop Tables Temp,Temp1;
Hi Sujjeth,
ThankYou.
But if you see my requirement it is different. Because few columns should come under one field and few should come in another filed.
Hi Anbu,
Thank You.
I will check and will let you know.
But if My Tab and Col increases to Tab10 and COl10 then Should I write code(10 times concatenate) as per your above code?
Will that be a fine?
Can you suggest me some script which checks dynamically and will do the needful without concatenating everytime.
If number of columns(Tab10 and COl10) won't change, then you can use 10 concatenate
Ok. May i know why you have used Autonumber?
Because Autonumber will give random number. But i want 1 2 3 ,.... in Number field
How to handle n rows
See this
Tab1:
LOAD * INLINE [
Product , Time1, Time2 , Emx , Loc , Value , Tab1 , Tab2 ,Tab3 , Tab4 ,Col1 , COl2 , Col3
X , 20-3-14 , 14-03-10 , ABC , 12 , 30 , A , B , C , DD , 1 , 2 , 3
Y , 20-3-14 , 14-03-10 , ABC , , 12 , 40 , D , B , C , DD , 7 , 4 , 5
];
Tab2:
CrossTable(Tab,Y,1)
LOAD
Product , Tab1 , Tab2 ,Tab3 , Tab4
Resident Tab1;
//DROP table Tab1;
Tab3:
CrossTable(Col,X,1)
LOAD
Product, Col1 , COl2 , Col3
Resident Tab1;
DROP table Tab1;