Load Script (date difference)

[leon511920]

Hi, I write the following calculation in the load script, the expected result will not be returned and only part of the data will be returned. Is the description of the date comparison correct?

[Script]
If(Date([date],'YYYY/MM/DD hh:mm:ss') = Date([maxdate],'YYYY/MM/DD hh:mm:ss'), [sales])

[maxdate] has only one date, so should I use variables?

[henrikalmen]

Are [date] and [maxdate] fields in the table you are loading? And are the values in those fields actually dates, in the same date format?

[leon511920]

Thank you so much. 

My script is below. I think date calculation is okay, but  when I calculated ([Diff_First] - [Diff_Second]) from  Table "Diff",
it can't calculate and the result is empty.....

When I check the [Diff_First] and [Diff_Second] separately, they have data. but can't calculate ([Diff_First] - [Diff_Second]).


[data example]（"-":hyphen）
[Diff_First].   | [Diff_Second]
102                             -
200                             -
312                             -
356                             -
209                             -
192                             -
 -                             202
 -                             109
 -                             305
 -                             294
 -                             123
 -                             934

[henrikalmen]

Diff_First and Diff_Second are on separate rows, to calculate their difference they need to be on the same row. Take a look at my answer to this similar topic, you should probably do something similar: https://community.qlik.com/t5/App-Development/Difference-between-two-dates-when-matching-another-fie...

[leon511920]

Thank you so much. As you say, the link is the same problem.

Your answer is adding the highlight part of the script?
(I'm not sure about your answer....sorry...)

[henrikalmen]

No, that's not right. You need to read up on how joins work. https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref... Joining tables adds columns, now you are trying to join with just a key column but no columns are added and that doesn't do anything at all. And there are other things as well in your script that raises questions...

I advise you to step through your script and look at the results for each step - you could for example add a line "exit script;" after your first join and look at the data model to see if it is as you expected. Then you move the "exit script" line after the next statement and reload and check your data model again. If you verify your data after each step you will notice when things happen that you did not expect. It will be easier to debug your script, and you'll learn a lot along the way.