Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
akpofureenughwu
Contributor III

Help resolving the loops in my apps

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

1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor III

Re: Help resolving the loops in my apps

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

3 Replies
marcus_malinow
Valued Contributor III

Re: Help resolving the loops in my apps

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
Contributor III

Re: Help resolving the loops in my apps

Thank you Marcus.

marcus_malinow
Valued Contributor III

Re: Help resolving the loops in my apps

My pleasure, glad it was helpful.