Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join problem from two different files

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.

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

9 Replies
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

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;


Capture.PNG

MarcoWedel

Hi,

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

QlikCommunity_Thread_215985_Pic1.JPG

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

Not applicable
Author

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


Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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