Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
nishanthi_8
Creator
Creator

How to compare each row in a excel sheet from load editor in Qlik Sense ?

I need to compare each row in a excel sheet to compare the dates, for example I have 100 rows and I need to compare 2 fields that is starting and ending date. out of which starting and ending date should point to a particular month i.e, I have 100 rows and date field contains 12 months were I need to compare the rows which has 'May' month alone. Is there any way to do this ?

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

You can try this script.

Load Starting_Date, Ending_Date, If(Starting_Date>=Ending_Date and Month(Starting_Date)='May',1,0) as Flag

From XYZ;

Now on front end you just need to do the Sum(Flag) to get the count of such entry.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

5 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

It would be great if you can share some sample data and give example from that, like what you want.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
nishanthi_8
Creator
Creator
Author

Starting Date          Ending Date

01-02-2016              3-02-2016

05-01-2016              03-01-2016

10-05-2016             10-05-2016

26-05-2016              01-06-2016

31-12-2016              28-12-2016

Here what I need to find is if(starting date >= ending date and month='May') then count(ID) . I need a solution to compare row by row

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

You can try this script.

Load Starting_Date, Ending_Date, If(Starting_Date>=Ending_Date and Month(Starting_Date)='May',1,0) as Flag

From XYZ;

Now on front end you just need to do the Sum(Flag) to get the count of such entry.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
marcus_sommer

If you want to compare your excel rawdata with the data which you loaded into qlik you should take a table-object and not the load-editor by adding an unique record-identifier within the table - if no one exists you create one with recno() or rowno().

To match your start- and end-dates to a master-calender you will need something like this: https://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch.

- Marcus

nishanthi_8
Creator
Creator
Author

thanks for the solution