Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

Date Format

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"

18 Replies
qlikview979
Specialist
Specialist
Author

Hi Kushal,

I want now  4/1/2016 as list box name.

settu_periasamy
Master III
Master III

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

The Generic Load

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 )

qlikview979
Specialist
Specialist
Author

Hi Settu_Periasamy,

Superb, Its working.

Regards,
Mahesh.

qlikview979
Specialist
Specialist
Author

Hi All,

Thanks for your Support.

Regards,

Mahesh.

Kushal_Chawda

try cross table

Loading Cross Tables

qlikview979
Specialist
Specialist
Author

Hi Kushal,

Thanks for your support,  I got the output.

Regards,

Mahesh.

qlikview979
Specialist
Specialist
Author

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.

qlikview979
Specialist
Specialist
Author

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.

settu_periasamy
Master III
Master III

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;