Convert text to date.

[Bobi]

Hi. I have an excel-file with date in the format of:

January -results - february-results  - march results and so on. And one column for revenue and one column for year.

I tried to solve it by data manager. First unpivoting the table to get one column with january, february and so on. Then spliting the columns to get february, january and so on splitted from january-results. Then creating a calculated field year column+&'-'&-(the new month column). Problem is I can still not make qlik sense understand January, february and so on are in date-format.

Anyone having any solution or maybe a more efficent to solve this problem from the beginning? 

(Another basic question, how do I extract the data from the manipulated tables in data manager so I can manipulate them further in the data load editor)? I Only managed to connect to the original file located on the desktop.)

[dplr-rn]

Share a sample of your data.

But on a high level youuse date# function 

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpretation...

e.g.

Date(Date#([DATE MOV],'YYYYMMMMDD'),'DD/MM/YYYY') as [DATE MOV] 

[Bobi]

I am not allowed to share data, but it looks like this.

Columnnames: Revenue - Company - During january - During february - During march    Year

                                   1000          company A     500                           200                           300                     2006

                                   400            company b       200                       100                               100                  2007

[dplr-rn]

Use script like below

date in csv like below

Company , During january ,During february ,During march, Year
company A, 500, 200, 300,2006
company b ,200,100, 100, 2007

Temp:
CrossTable(Month,Sales,2)
LOAD
    Company,
    "Year",
    "During january",
    "During february",
    "During march"
    
FROM [lib://Downloads/Sample.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

NoConcatenate

load 
	Company,
    "Year",
   SubField(Month,' ',2) as Month,
    Sales,
    Date(Date#('01-'&SubField(Month,' ',2)&'-'&Year ,'dd-MMMM-yyyy'),'DD-MMM-YYYY') as NewDate

resident Temp;

drop table Temp;

result

[dplr-rn]

result