topic Re: How to combine three fields in to one field from same table in QlikView

How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 07:59:27 GMT

Hi,

Hope you are doing great!

Can any one please explain how to combine multiple fields into one field?

Please find the attached excel example data.

from timestamp field i need only date and need to combine in to one field.

Thanks for advance.

Re: How to combine three fields in to one field from same table

pradosh_thakur — Thu, 18 Jan 2018 08:05:08 GMT

for this what need to be the output

2017-10-24 17:43:31

2017-10-25 19:26:37

in rowno 11 there are multiple dates which dates need to be there ? or you want both the dates ?

Re: How to combine three fields in to one field from same table

vangurideepu — Thu, 18 Jan 2018 08:07:46 GMT

Hi Kumar,

try like this, Using concatenate symbol

we can combine the fields

CapturedDate1&'|'&CapturedDate2&'|'&CampturedDate3 as FinalDate,

Regards

Pradeep

Re: How to combine three fields in to one field from same table

kenphamvn — Thu, 18 Jan 2018 08:08:51 GMT

Please give expected result for this data

Regards

An Pham

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 08:57:55 GMT

Thanks for your response.

That fields all are Timestamps, from that i need only Date and need to combine the three fields in too one fields.

Like this,

23.10.2017

24.10.2017

25.10.2017

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 09:05:53 GMT

Thanks Pradeep,

Can you please find below scripts.It's showing wrong.

please advice.

TRIP:

LOAD

Date1 &'|'& Date2 &'|'& Date3 as finalDate,

[Carrier Name],

[Carrier Code],

DATE(DayStart([Stage1Captured Time], 'DD.MM.YYYY')) as Date1,

DATE(DayStart([Stage2Captured Time], 'DD.MM.YYYY')) as Date2,

DATE(DayStart([Stage3Captured Time], 'DD.MM.YYYY')) as Date3,

FROM

(...

(ooxml, embedded labels, table is [Trip Report]);

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 09:07:40 GMT

Thanks for your response Pham,

Its should be only one date. Excludes time.

FinalDate

23.10.2017

Re: How to combine three fields in to one field from same table

shanky1907 — Thu, 18 Jan 2018 09:12:42 GMT

Try preceding load:

TRIP:

LOAD

Date1 &'|'& Date2 &'|'& Date3 as finalDate,

[Carrier Name],

[Carrier Code],

DATE(DayStart([Stage1Captured Time], 'DD.MM.YYYY')) as Date1,

DATE(DayStart([Stage2Captured Time], 'DD.MM.YYYY')) as Date2,

DATE(DayStart([Stage3Captured Time], 'DD.MM.YYYY')) as Date3;

LOAD

[Carrier Name],

[Carrier Code],

Stage1Captured Time,

Stage2Captured Time,

Stage3Captured Time

FROM

(...

(ooxml, embedded labels, table is [Trip Report]);

How to combine three fields in to one field from same table

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 09:30:59 GMT

Hi Shashank,

Its not working.

LOAD *,

Date1 &'|'& Date2 &'|'& Date3 as finalDate;

LOAD *,

DATE(DayStart([Stage1Captured Time], 'DD.MM.YYYY')) as Date1,

DATE(DayStart([Stage2Captured Time], 'DD.MM.YYYY')) as Date2,

DATE(DayStart([Stage3Captured Time], 'DD.MM.YYYY')) as Date3;

LOAD

[Carrier Name],

[Carrier Code],

Stage1Captured Time,

Stage2Captured Time,

Stage3Captured Time

FROM

(...

(ooxml, embedded labels, table is [Trip Report]);

Re: How to combine three fields in to one field from same table

shanky1907 — Thu, 18 Jan 2018 09:33:56 GMT

What is the error?

Re: How to combine three fields in to one field from same table

pradosh_thakur — Thu, 18 Jan 2018 09:49:16 GMT

are you expecting the result of this script?

note : your last column is camptured date in the sheet :d :d :d

LOAD DISTINCT StoreID,

ManagerName,final_date

WHERE LEN(TRIM(final_date))>0

;

load StoreID,

ManagerName,date#(SubField(CapturedDate1_date &','& CapturedDate2_date & ',' & CampturedDate3_date,','),'MM/DD/YYYY') as final_date

;

LOAD StoreID,

ManagerName,

date(floor(TIMESTAMP#(CapturedDate1,'YYYY-MM-DD hh:mm:ss')))as CapturedDate1_date,

date(floor(TIMESTAMP#(CapturedDate2,'YYYY-MM-DD hh:mm:ss'))) as CapturedDate2_date,

date(floor(TIMESTAMP#(CampturedDate3,'YYYY-MM-DD hh:mm:ss'))) as CampturedDate3_date,

CapturedDate1,

CapturedDate2,

CampturedDate3

FROM

Sampledata.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 09:57:16 GMT

no errors but there is no data in fields, finalDate, Date1,Date2,Date3

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 10:21:25 GMT

Thanks for your help Pradhosh,

Its working.

can i see the dates from starting. like if the date is started from July. It should show like this and format also.

need to see ' . ' (dot) Instead of '-' . How to remove that space in DO Number listbox?

02.05.2017

10.05.2017

06.06.2017 etc.

Re: How to combine three fields in to one field from same table

pradosh_thakur — Thu, 18 Jan 2018 10:33:46 GMT

LOAD DISTINCT StoreID,

ManagerName,final_date

WHERE LEN(TRIM(final_date))>0

;

load StoreID,

ManagerName,date(date#(SubField(CapturedDate1_date &','& CapturedDate2_date & ',' & CampturedDate3_date,','),'MM/DD/YYYY').'DD.MM.YYYY') as final_date

;

LOAD StoreID,

ManagerName,

date(floor(TIMESTAMP#(CapturedDate1,'YYYY-MM-DD hh:mm:ss')))as CapturedDate1_date,

date(floor(TIMESTAMP#(CapturedDate2,'YYYY-MM-DD hh:mm:ss'))) as CapturedDate2_date,

date(floor(TIMESTAMP#(CampturedDate3,'YYYY-MM-DD hh:mm:ss'))) as CampturedDate3_date,

CapturedDate1,

CapturedDate2,

CampturedDate3

FROM

Sampledata.xlsx

(ooxml, embedded labels, table is Sheet1);

IN THE LIST BOX SORT IT BY final_date to see it sorted .

for DO Number use if(len(trim([ DO Number]))>0,[ DO Number]) as expression in list box

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 10:47:02 GMT

script is not reloading, strucked at initially only. I have lot of fields in my table.

load

[Trip Id],

[Vehicle Number],

Status,

[Movement Type],

[Do Number],

Plant1,

[Carrier Name],

[Carrier Code],

[Stage1 Driver Id],

[Stage1First Name],

[Stage1Last Name],

[Stage1Truck Compliance],

[Stage1Driver Compliance],

[Stage1User Id],

[Stage1Device Id],

[Stage1Approved/Rejected By],

[Stage1Approved/Rejected Date],

[Stage2Driver Id],

[Stage2First Name],

[Stage2Last Name],

[Stage2Truck Compliance],

[Stage2Driver Compliance],

[Stage2User Id],

[Stage2Device Id],

[Stage2Approved/Rejected By],

[Stage2Approved/Rejected Date],

[Stage3Driver Id],

[Stage3First Name],

[Stage3Last Name],

[Stage3Truck Compliance],

[Stage3Driver Compliance],

[Stage3User Id],

[Stage3Device Id],

[Stage3Approved/Rejected By],

[Stage3Approved/Rejected Date],

date((SubField(CapturedDate1_date &','& CapturedDate2_date & ',' & CampturedDate3_date,','),'MM/DD/YYYY'),'DD.MM.YYYY')as final_date

;

Re: How to combine three fields in to one field from same table

pradosh_thakur — Thu, 18 Jan 2018 12:25:19 GMT

You are not following the way i have described . Please follow and it shall work.

Re: How to combine three fields in to one field from same table

rkpatelqlikview — Thu, 18 Jan 2018 12:30:34 GMT

Thanks for your time and help Pradosh,

Its working now