Join problem from two different files

[#1]

Dear all,

I have tried to combine excel file with a notepad file. The intention is to combine both data sources based on the field 'YYYYMMDD'.

/*Excel file.*/

Input:

LOAD

MakeTime(hour(time((time( (mid(startDate, 12,5)),'hh:mm')  + (time((trainingTimeAbsolute*0.6/3600)/24/60,'hh:mm')) + (60/1440)),'HH'))) as HH,

   mid(startDate, 29,5)&

    if(mid(startDate, 5,3) = 'Apr', 04,

       if(mid(startDate, 5,3) = 'Mar', 03,

       if(mid(startDate, 5,3) = 'Feb', 02,

       if(mid(startDate, 5,3) = 'Mei', 05,

       if(mid(startDate, 5,3) = 'Jun', 06,

       if(mid(startDate, 5,3) = 'Jul', 07,

       if(mid(startDate, 5,3) = 'Aug', 08,

       if(mid(startDate, 5,3) = 'Sep', 09,

       if(mid(startDate, 5,3) = 'Okt', 10,

       if(mid(startDate, 5,3) = 'Nov', 11,

  12 ))))))))))

  &

    if (  replace(mid(startDate, 8,3),' ','') < 10, '0'&replace(mid(startDate, 8,3),' ',''),replace(mid(startDate, 8,3),' ','') )

  as YYYYMMDD

FROM

/*File  = Date problem.xlsx  and Date problem2.xlsx*/

(ooxml, embedded labels, table is Blad1);

/*Notepad data*/

Cabauw:

LOAD [# STN],

     YYYYMMDD,

     MakeTime(hour(time(HH&':00','HH:MM'))) as HH,

     DD

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq,header is 31 lines);

Combined:

NoConcatenate LOAD HH,

     YYYYMMDD

     Resident Input;

left JOIN

LOAD

     *

     Resident Cabauw;

Drop Table Cabauw, Input;

When reading excel file "Date problem.xlsx" everything works fine

However when excel file "Date problem2.xlsx" was used, then no match has been found by QV:

The difference between both files are teh date: 16-04-2016 and 8-04-2016. (dd-mm-yyyy).

I have tried everything but do not found the solution.

Could you please advice?

Thanks and looking forward.

Best regards,

Cornelis.

[marcus_sommer]

The reason will be that both fields contain not the content/format. If you put a small source-statement to your loadings, like: 'xlsx' as Source and 'txt' as Source you could compare both fields within the gui.

Beside them it's not necessary to extract time and date with such heavy string-functions - you could use for this converting-functions like date#() and timestamp#() and then you get a real date-field which will be surely join correctly and you could it associate to a master-calendar, see for this:

Get the Dates Right

The Date Function

How to use - Master-Calendar and Date-Values

- Marcus

[sunny_talwar · Accepted solution]

I think you made your script very complicated. Can you try this:

Input:

LOAD Hour(Mid(startDate, 11,5) + ((trainingTimeAbsolute * 0.6/3600)/24/60) + (60/1440))  as HH,

  Date(MakeDate(SubField(startDate, ' ', -1), Month(Date#(SubField(startDate, ' ', 2), 'MMM')), SubField(startDate, ' ', 3)), 'YYYYMMDD') as YYYYMMDD

FROM

[Date problem2.xlsx]

(ooxml, embedded labels, table is Blad1);

Cabauw:

LOAD [# STN],

    Date(Date#(YYYYMMDD, 'YYYYMMDD'), YYYYMMDD) as YYYYMMDD,

    Hour(Time#(HH, 'h')) as HH,

    DD

FROM

[Cabauw date.txt]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq,header is 31 lines);

Right Join

LOAD *

Resident Input;

DROP Table Input;

[MarcoWedel]

Hi,

if you also need separate Day, Month, Year and Time fields from your Timestamp, one solution might be:

table1:
LOAD *,
    Timestamp(YYYYMMDD+startTime)                          as startTimestamp;
LOAD *,
    Date(MakeDate(startYear,startMonth,startDay),'YYYYMMDD') as YYYYMMDD;
LOAD *,
    Month(Date#(SubField(startDate,' ',2),'MMM'))            as startMonth,
    SubField(startDate,' ',3)                                as startDay,
    Time#(SubField(startDate,' ',4),'hh:mm:ss')              as startTime,
    SubField(startDate,' ',5)                                as startTimeZone,
    SubField(startDate,' ',6)                                as startYear
INLINE [
    startDate
    Sat Apr 16 13:46:39 GMT+0200 2016
    Fri Apr 8 05:28:34 GMT+0200 2016
];

hope this helps

regards

Marco

[#5]

Dear Sunny,

Thank you fior your swift reply. The

Date(MakeDate(SubField(startDate, ' ', -1), Month(Date#(SubField(startDate, ' ', 2), 'MMM')), SubField(startDate, ' ', 3)), 'YYYYMMDD') as YYYYMMDD

is indeed far more efficient.

However, the script does not work for Date problem.xlsx. The problem can be ascribed to the empty space in a string of the hour representation:

Hour(time((replace((mid(startDate, 11,9)), ' ','')),'hh:mm') + ((trainingTimeAbsolute * 0.6/3600)/24/60) + (60/1440))  as HH,

If there is an empty space in a string, then the HH will not match from Notepad file.

Thank you for your excellent support, this is a massive improvement and a learning point.

Best regards,

Cornelis

[#6]

Hello Marcus,

Sunny gave the responss but you are right that correct date representation and efficient QV script programming are key to proceed,

Best regards,

Cornelis

[#7]

Hi Marco,

Execellent, a new learning point, again! Correct date definition is important.

Thank you for your effort.

Best regards,

Cornelis.

[sunny_talwar]

First of all, I am glad we were able to help you learn something new.

Second, it is true that extra spaces may throw off results. Are you looking for a solution that will work for both your Excel files? I am sure we can make it work, but the bigger question is that are there more variations? If not, then may be it does make sense to use Alt() function to get it work. If there are more you might want to get this issue resolved in the Excel file.

Best,

Sunny

[#9]

Hi Sunny,

No, there are not much variation. The difference is only 1 empty space in a string and not more than that.

Fri Apr 8 05:28:34 GMT+0200 2016

Sun Apr 10 05:28:34 GMT+0200 2016

I have applied your solution in a a large Qlikview application and tested for all date ranges and it works excellent.

Thank you for your valuable contribution. Especially the cut-and-dried supplied solution is helpful.

Best regards,

Cornelis.

[sunny_talwar]

Awesome