Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Please find the attached Document.
when i am loading to excel from "H column to CT column "i am getting header is number format . here
I Want Source file headers with dates from "H column to CT column"
Hi Kushal,
I want now 4/1/2016 as list box name.
Hi Mahesh,
you can just use the cross load and you will get the Date in single column.
Still if you want the date as header (when we load from excel it will convert to number), you need to look on Generic load also..
1. Need to use the Cross Table
2. Convert the date and use the Generic Load
3. Combine all the Generic Table into one
4. Left join with your original table.
Directory;
T1:
CrossTable(Date, Data, 7)
LOAD *
FROM
[Reports Time Lines Updated - Copy - Copy.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Generic:
Generic LOAD
[SL #],
Date(Trim([Date])) as Date,
Time(Trim([Data]),'hh:mm') as Data
Resident T1;
CombinedGenericTable:
Load * Inline [
Temp
''
];
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'Generic.*') THEN
LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
DROP Field Temp;
Left Join(CombinedGenericTable)
LOAD [SL #],
[Activity / Deliverable],
[Duration ( in Mins )],
[Frequency],
[Report Owners],
[Report Time Line],
[Report/Upload/Activity] Resident T1;
DROP Table T1;
Sample Attached (just i added the data in excel also )
Hi Settu_Periasamy,
Superb, Its working.
Regards,
Mahesh.
Hi All,
Thanks for your Support.
Regards,
Mahesh.
try cross table
Hi Kushal,
Thanks for your support, I got the output.
Regards,
Mahesh.
Hi,
plase find the attached file,
I got correct output, but SL# no:- 7 is missing
i have checked that but i am not getting can you check once .
Regards,
Mahesh.
Hi,
if i comment the left join table i am getting correct output. if i comment that any problem is there. can you tell me.
Regards,
Mahesh.
One more method from Here remove ## from field names
T1:
LOAD *
FROM
[Reports Time Lines Updated - Copy.xlsx]
(ooxml, no labels, table is Sheet1) Where RecNo()=2;
tabHeaders_temp:
CrossTable (NameOld, NameNew)
LOAD 1,* Resident T1 Where RecNo()=1;
DROP Table T1;
mapFieldNames:
Mapping LOAD NameOld,if(IsNull(Date(NameNew)),NameNew,
Date(NameNew,'M/D/YYYY')) as NameNew1 Resident tabHeaders_temp;
DROP Table tabHeaders_temp;
New:
LOAD * FROM
[Reports Time Lines Updated - Copy.xlsx]
(ooxml, no labels, header is 2 lines, table is Sheet1);
RENAME Fields using mapFieldNames;