topic Re: Date Format in QlikView

Date Format

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

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"

Re: Date Format

Pulkit_Thukral — Tue, 17 May 2016 05:34:04 GMT

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

Re: Date Format

qlikview979 — Tue, 17 May 2016 05:36:51 GMT

I want same headers in qlikview also.

Re: Date Format

settu_periasamy — Tue, 17 May 2016 06:10:54 GMT

Hi,

May be check here..

read header date field as is in source

Re: Date Format

qlikview979 — Tue, 17 May 2016 06:12:19 GMT

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

Re: Date Format

Kushal_Chawda — Tue, 17 May 2016 06:25:13 GMT

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

Re: Date Format

qlikview979 — Tue, 17 May 2016 06:57:10 GMT

Hi Kushal,

I tried like that i got like this

but i want that date as a header name.

Re: Date Format

qlikview979 — Tue, 17 May 2016 06:59:14 GMT

Hi Kushal,

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

Re: Date Format

qlikview979 — Tue, 17 May 2016 06:59:14 GMT

Hi Kushal,

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

Re: Date Format

qlikview979 — Tue, 17 May 2016 06:59:14 GMT

Hi Kushal,

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

Re: Date Format

qlikview979 — Tue, 17 May 2016 06:59:14 GMT

Hi Kushal,

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

Re: Date Format

settu_periasamy — Tue, 17 May 2016 07:36:39 GMT

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 )

Re: Date Format

qlikview979 — Tue, 17 May 2016 07:43:26 GMT

Hi Settu_Periasamy,

Superb, Its working.

Regards,
Mahesh.

Re: Date Format

qlikview979 — Tue, 17 May 2016 07:44:59 GMT

Hi All,

Thanks for your Support.

Regards,

Mahesh.

Re: Date Format

Kushal_Chawda — Tue, 17 May 2016 07:47:04 GMT

try cross table

Loading Cross Tables

Re: Date Format

qlikview979 — Tue, 17 May 2016 09:23:46 GMT

Hi Kushal,

Thanks for your support, I got the output.

Regards,

Mahesh.

Re: Date Format

qlikview979 — Tue, 17 May 2016 09:36:04 GMT

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.

Re: Date Format

qlikview979 — Tue, 17 May 2016 09:45:34 GMT

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.

Re: Date Format

settu_periasamy — Tue, 17 May 2016 13:38:37 GMT

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;