Help resolving the loops in my apps

[akpofureenughwu]

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

[marcus_malinow · Accepted solution]

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);

[akpofureenughwu]

Thank you Marcus.

[marcus_malinow]

My pleasure, glad it was helpful.