Help resolving the loops in my apps

akpofureenughwu — Wed, 30 Aug 2017 12:01:43 GMT

In a bid to sync two dates in my development, I followed the scripts using in this discussion https://community.qlik.com/thread/222435

I have one or more than one loops in my apps. Please I need someone to help me inspect my codes and probably debug it

Remember my aim is to link expenditure date and cash date to become one.

See attached files

Thank you

Akpofure

Re: Help resolving the loops in my apps

marcus_malinow — Wed, 30 Aug 2017 12:21:52 GMT

Try this script:

Expenditure:

LOAD [Expenditure Date],

Location,

Location & '_' & [Expenditure Date] as %Link,

[Expenditure Item],

[Expenditure Figure (Naira) PER],

[Expenditure Figure (Naira) YTD],

[Expenditure Figure (Naira) ITD],

[Working Capital Item],

[Working Capital Item Figure (Naira) PER],

[Working Capital Item Figure (Naira)YTD],

[Working Capital Item Figure (Naira) ITD]

FROM

(ooxml, embedded labels, table is [Expenditure Table ]);

Cash:

LOAD [Cash Date],

Location,

Location & '_' & [Cash Date] as %Link,

Partner,

Equity,

[PER NGN],

[YTD NGN],

[ITD NGN]

FROM

(ooxml, embedded labels, table is [Cash Call Table]);

LINK:

LOAD DISTINCT

[Expenditure Date] as %Date,

Location,

%Link

RESIDENT Expenditure;

CONCATENATE (LINK)

LOAD DISTINCT

[Cash Date] as %Date,

Location,

%Link

RESIDENT Cash;

DROP FIELDS [Expenditure Date], Location FROM Expenditure;

DROP FIELDS [Cash Date], Location FROM Cash;

MinMaxDates:

LOAD

min(num(fieldvalue('%Date',recno())))-1 as mindate,

max(num(fieldvalue('%Date',recno()))) as maxdate

AUTOGENERATE

fieldvaluecount('%Date');

LET vCalendarStart = num(peek('mindate', -1, 'MinMaxDates'));

LET vCalendarEnd = num(peek('maxdate', -1, 'MinMaxDates'));

DROP TABLE MinMaxDates;

Calendar:

Load

TempDate AS %Date,

TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Date(MonthStart(TempDate), 'YYYY-MMM') as YearMonth,

Month(TempDate) As Month,

Dual(WeekYear(TempDate) & '-' & Right('0' & Week(weekstart(TempDate)), 2), Floor(WeekStart(TempDate))) as YearWeek,

Day(TempDate) As Day,

WeekDay(TempDate) as WeekDay

;

LOAD

$(vCalendarStart) + Iterno() As Num,

Date($(vCalendarStart) + IterNo()) as TempDate,

IterNo() as DayCounter

AutoGenerate 1

While $(vCalendarStart) + IterNo() <= $(vCalendarEnd);

Re: Help resolving the loops in my apps

akpofureenughwu — Wed, 30 Aug 2017 13:21:00 GMT

Thank you Marcus.

Re: Help resolving the loops in my apps

marcus_malinow — Wed, 30 Aug 2017 13:22:34 GMT

My pleasure, glad it was helpful.

topic Help resolving the loops in my apps in QlikView

Help resolving the loops in my apps

Re: Help resolving the loops in my apps

Re: Help resolving the loops in my apps

Re: Help resolving the loops in my apps