topic Re: Nested For in QlikView

Nested For

jood_ahmad — Tue, 11 Aug 2015 09:46:37 GMT

Dears,

Can any one help me in this:

i need to know how we can use the for loop in qlikview(load script) let's say i need to count the people or customer thy make Orders after March 2015 to the current day or in the previous 120 day before .

I imagine we should have for lope one of them to check the customer in this days and the second to store or count it and store it in another Variable; but i try to do it , unfortunately not work.

if i need to know the Customer# in the previous 4 month from the current date selection @and if i need to know the customer by check the New Customers, Old Customers further split into active inactive customers (definition of inactive anyone who has not been in system for 4 months) and this i thing will happened by check the [Creation Date2] and [ Last Order Date2] with the selection time period.

Any one had something like this before.

please find the attach sample data from my table.

Message was edited by: Ahmad Kastero

Re: Nested For

Anonymous — Tue, 11 Aug 2015 09:50:26 GMT

can you post example of your script?

Re: Nested For

jonathandienst — Tue, 11 Aug 2015 09:58:42 GMT

Your question is not very clear. You certainly do not need a loop to count something, but perhaps there is something about your source data. I suggest that you post some sample data and the output that you would like to see from that data.

Re: Nested For

isorinrusu — Tue, 11 Aug 2015 10:36:00 GMT

As far as I understand, you don't actually need a loop: what you need is to restrict the data to the last 4 months.

So, for example, you'd have:

Last4MonthsSales:

Load Customer,

Transaction,

Company,

Date

From Sales

Where Date<(Today()-120)

That would create a Table where only data from the last 4 months is available. Is that what you need?

Re: Nested For

jood_ahmad — Tue, 11 Aug 2015 10:47:38 GMT

Thank you for replay.

it is part of what i need to get a customer in -120 but also i need to know if these customer in last 4month is a new customer or old customer ; it will help to know the customer behavior if he was inactive customer and after the Campaigns he become active customer ; this what i need.

Re: Nested For

isorinrusu — Fri, 14 Aug 2015 06:40:01 GMT

Ahmad, look at the qvw attached.

It seems like all customers are active in your data.

Re: Nested For

jood_ahmad — Sun, 16 Aug 2015 16:51:16 GMT

Thanks isorinrusu‌ there is some customer active and the other inactive but can you explain the expression to see if i understand it correctly :

if(Date(max([ Last Order Date2]))<=Date($(vCreationDateLast4Months)),'Inactive','Active')

the first part to get the max for last order date and check it with ($(vCreationDateLast4Months)) then it will get Inactive OR Active but i am sorry for this question (how it will compare it just compare date with date but we should compare or check the (customer id) or (customer phone number) through our criteria if it is exist in that time period it will be Active else it will be Inactive)

Re: Nested For

isorinrusu — Mon, 17 Aug 2015 11:31:52 GMT

Ahmad, can you give me two examples of customers, with all their fields that you use for the calculation, one being active and the other one inactive?

Re: Nested For

jood_ahmad — Mon, 17 Aug 2015 12:17:12 GMT

what is you did in the Help Qvw is perfect but when i try to count that field to get # of Customer Active and # of Customer Inactive in text box give me error in expression Nested aggregation not allowed:

Inactive==>

Customer ID=200229 // Branch ID=2 // Last_Order_Date =2014-03-28 // Creation_Date = 2013-09-27 // Cust_Phone_Numbr =0533389466

Active==>

Customer ID=200008 // Branch ID=2 // Last_Order_Date =2015-07-04 // Creation_Date = 2013-09-05 // Cust_Phone_Numbr =0504736988