Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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"

1 Solution

Accepted Solutions
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 )

View solution in original post

18 Replies
Pulkit_Thukral
Partner - Creator II
Partner - Creator II

Hi,

Because of the repetitive days the format is getting changed.

Better use date keyword before all if you want to modify at qlikview level.

EX:

LOAD

    Fri,

     Sat,

     Sun,

     Mon,

     Tue,

     Wed,

     Thu,

     Date(Fri1),

     Date(Sat1),

     Date(Sun1),

     Date(Mon1),

.....

Or other wise i would suggest to get the source only modified to generate a different excel with just 1 header row rather than 2

qlikview979
Specialist
Specialist
Author

Hi

I want same headers in qlikview also.

settu_periasamy
Master III
Master III

qlikview979
Specialist
Specialist
Author

Hi,

Can anyone help me?

my excel headers are like this

01/04/16

02/04/16

03/04/16

04/04/16

loading  my source file in qlikview i am getting header like this

     42461,

     42462,

     42463,

     42464

but i need  exact  headers like this

01/04/16

02/04/16

03/04/16

04/04/16

Regards,

Mahesh

Kushal_Chawda

Go to excel file, select all the Date header using ctrl +shift + ->  right click -> Format cells -> select Date and select Date format apply ok. Now try to load

qlikview979
Specialist
Specialist
Author

Hi Kushal,

I tried like that i got like this

Untitled1.png

but i want that date as a header name.

qlikview979
Specialist
Specialist
Author

Hi Kushal,

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

qlikview979
Specialist
Specialist
Author

Hi Kushal,

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

qlikview979
Specialist
Specialist
Author

Hi Kushal,

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