Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
nishanthi_8
Not applicable

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
kaushik_solanki
Not applicable

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

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

5 Replies
kaushik_solanki
Not applicable

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

Hi,

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

Regards,

Kaushik Solanki

nishanthi_8
Not applicable

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

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

kaushik_solanki
Not applicable

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

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

marcus_sommer
Not applicable

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

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
Not applicable

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

thanks for the solution