Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
is there any way to split multiple dates from each cell and produce corresponding records.
E.g in row given below I want one date in one single row. This is very critical for me. Please Help
49 | $10,634.96 | 2016/09/17: 0.5 hrs Total: 0.5 hrs | 2016/09/29: 1 hrs Total: 1 hrs | 2016/09/28: 2 hrs Total: 2 hrs | 2016/04/22: 0.5 hrs 2016/06/20: 0.5 hrs 2016/10/11: 0.5 hrs Total: 1.5 hrs |
Thanks
Could be there? Can you provide more information related the data
sample is given below
Raw Data(all in one row)
2016/09/28: 0.5 hrs Total: 0.5 hrs | 2016/09/28: 1 hrs Total: 1 hrs | 2016/09/28: 1.75 hrs 2016/10/28: 1hrs Total: 1.75 hrs | 2016/05/19: 0.5 hrs 2016/08/16: 0.5 hrs 2016/11/23: 0.5 hrs Total: 1.5 hrs |
Loaded in QlikSense Should like (in separate rows)
Name | Date | Hrs | Date | Hrs | Date | Hrs | Hrs | |
Name1 | 9/28/2016 | 0.5 | 9/28/2016 | 1 | 9/28/2016 | 1.75 | 2016/05/19 | 0.5 |
Name1 | 10/28/2016 | 2 | 8/16/2016 | 0.5 | ||||
Name1 | 11/23/2016 | 0.5 |
thanks
use subfield function:
SubField( F,chr(10)) as NewInvoice
Replace F with your Field Name which you want to split and chr(10) with your delimiter. Pls refer attached.
Hi,
one solution might be:
tabTemp:
CrossTable (Col, String)
LOAD RecNo() as Name, *
FROM [https://community.qlik.com/thread/263794] (html, codepage is 1252, no labels, table is @2);
table1:
LOAD *,
AutoNumber(RowNo(),Name&'/'&Group) as DateNo
Where Date;
LOAD Name,
Group,
Trim(SubField(String,':',1)) as Date,
SubField(String,':',2) as Hrs;
LOAD Name,
PurgeChar(Col,'@') as Group,
SubField(String,'hrs') as String
Resident tabTemp;
DROP Table tabTemp;
hope this helps
regards
Marco
or using Qlik Sense:
regards
Marco
Thanks but its not gonna working. still on it
HI I am unable to attach file here. can I send u my subset of data otherwise.
That may offer you better. But marco gave simple and achievable solution. Why it's not going to work would you describe more. Please share sample data
I have around 60 columns and each one having variable number of dates with new line delimited something like
give blow as one cell. I need every date single row and every 0.5 (without hrs) single column.
2016/04/22: 0.5 hrs 2016/06/20: 0.5 hrs 2016/10/11: 0.5 hrs Total: 1.5 hrs |