Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community users
Can anyone suggest how do i format my Date field which is originally in this format (YYYY-MM-DD:hh:mm:SS) into two separate list boxes named Date and time?
my script is:
LOAD @1 as Country,
@5 as DATE,
@6 as Duration,
@11 as Technology
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Hi Ravi, try like this:
In script:
LOAD @1 as Country,
Date(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS'),'YYYY-MM') as Date,
time(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Time,
@6 as Duration,
@11 as Technology
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Now in front end create a bar chart:
In dimension:
Take month.
Inexpression:
1. count({<Technology={'2G'}>}Technology)
2.count({<Technology={'3G'}>}Technology)
Hope it helps:
Regards
KC
date(floor(@5)) as DATE
time(frac(@5)) as TIME
-Rob
Thanks for your reply Rob
Updating my script to this give me an error:
LOAD @1 as Country,
// @5 as DATE,
Date(floor@5) as DATE,
time(frac@5) as TIME,
@6 as Duration,
@11 as Technology
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Try like:
LOAD @1 as Country,
Date(floor(@5)) as DATE,
time(frac(@5)) as TIME,
@6 as Duration,
@11 as Technology
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Thanks
Loading the below script didn’t give any error but no data loaded in the list box for DATE and TIME
LOAD @1 as Country,
// @5 as DATE,
Date(floor(@5)) as DATE,
time(frac(@5)) as TIME,
@6 as Duration,
@11 as Technology
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Hi
if you want to do in list box then
Try adding list boxes and edit expressions as
fro date left (Date,8)
for time right(Dtae,7)
Regards
Harsha
Hi Ravi,
Try
Date(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Date,
time(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Time
or
Try like this:
left(@5,10) as date,
right(@5,8) as Time
Hope it works
Regards
KC
Hi Jyotish
That worked exactly the way i wanted. However can i just change my requirement slightly?
What if i want the date column just to display in YYYY-MM format?
Current Script is:
LOAD @1 as Country,
Date(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Date,
time(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Time,
@6 as Duration,
@11 as Technology
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
Hi Jyotish
Don’t worry i just used your Date script and modified by just swapping date with month and it worked almost close to what i wanted.
Month(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Month,
This is how my list boxes look now which is ok
I want to plot a bar graph showing monthly totals counts of 2G occurrences vs 3G occurrences.
For example for each country in the month of Sep count the number of times 2G appeared in the list box vs 3G.
I used Month and country as dimensions, and used Count of Technology but that just counts 2G 3G Unknown all together. I want counts of each technology.
Can you please help again?
Regards
Hi Ravi,
Use Subfield like:
=
SubField(@5,':',1) as Date;
SubField(@5,':',2)&':'&SubField(@5,':',3)&':'&SubField(@5,':',4)&':'&SubField(@5,':',5) as Time;