topic Re: Report in specific format Required in QlikView

Report in specific format Required

pra_kale — Sat, 31 Oct 2015 15:40:44 GMT

Hi All,

I am attaching Two sample excel files in one excel file i.e. Format_Require which is given below and this format I want my final out-put.

Policy_no	Date_1	Amt_1	Date_2	Amt_2
12345678	10-Sep-15		12-Sep-15
5678910	05-Sep-15		08-Sep-15
2345678	12-Sep-15		14-Sep-15
1108901	01-Oct-15		10-Oct-15

In the second excel file I have Policyno,Date,Amt.

I have trying by writing below given script but below given script is not giving desired result.

Directory;

LOAD Policy_no,

Date_1,

Amt_1,

Date_2,

Amt_2,

Policy_no & Date_1 as key1

FROM

Format_Require.xlsx

(ooxml, embedded labels, table is Sheet1);

left Join

LOAD Policyno,

Date,

Amt,

Policyno & Date as key1

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1);

Is there any by which I can get desired result.

Thanks in Advance.

Re: Report in specific format Required

Sat, 31 Oct 2015 16:18:43 GMT

Hi, could you more accurately describe what you want to accomplish.

regards

SGI

Re: Report in specific format Required

sunny_talwar — Sat, 31 Oct 2015 16:44:19 GMT

Try this:

Table:

LOAD Policyno,

Date,

Amt

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1);

Count:

LOAD Policyno,

Count(Date) as Count

Resident Table

Group By Policyno;

MaxCount:

LOAD Max(Count) as MaxCount

Resident Count;

LET vMaxCount = Peek('MaxCount');

DROP Table Count, MaxCount;

LET vJoin = '';

FOR i = 1 to $(vMaxCount)

FinalTable:

$(vJoin)

LOAD Policyno,

Date(Min(Date, $(i))) as Date$(i),

FirstSortedValue(Amt, Date, $(i)) as Amt$(i)

Resident Table

Group By Policyno;

LET vJoin = 'Join(FinalTable)';

// TRACE $(i);

NEXT

DROP Table Table;

Re: Report in specific format Required

pra_kale — Sun, 01 Nov 2015 07:09:41 GMT

Hi,

Thanks for your reply and help.

But when I copy paste your code in the script it gives me an error after For loop which is given below..I think some syntax error

FinalTable:

$(vJoin)

LOAD Policyno,

Date(Min(Date, $(i))) as Date$(i),

FirstSortedValue(Amt, Date, $(i)) as Amt$(i)

Resident Table

Group By Policyno;

shall I need to make any changes at my end...

secondly, by looking at the out-put which you have shared , this code might be giving results for all the dates from Amt excel sheet but I want results only for the dates mentioned in the Format_Require.xlsx.

for e.g. In case of policy no 12345678 I want the out-put only for 10-Sep-2015 and 12-Sep-2015...and not for 1-Sep-2015.

Thanks in advance.

Re: Report in specific format Required

sunny_talwar — Sun, 01 Nov 2015 12:56:22 GMT

Try this:

Table:

LOAD Policyno,

Date(Max(Date, 1), 'DD-MMM-YYYY') as Date2,

FirstSortedValue(Amt, -Date, 1) as Amt2

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1)

Group By Policyno;

Join(Table)

LOAD Policyno,

Date(Max(Date, 2), 'DD-MMM-YYYY') as Date1,

FirstSortedValue(Amt, -Date, 2) as Amt1

FROM

Amt.xlsx

(ooxml, embedded labels, table is Sheet1)

Group By Policyno;

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Len(Trim(Date1)) > 0;

DROP Table Table;

Output:

Re: Report in specific format Required

pra_kale — Sun, 01 Nov 2015 13:32:09 GMT

Hi,

Thank you vary much.. In current scenario the out-put is perfectly OK...

But as this is just e.g. and in reality I want the out-put as per the Format_Require sheet and in that I may get the cases where out-put between Two dates also require In that case how to handle ..can you pleases help in this as well...

like below given e.g. If I require the out-put.

Policy_no	Date_1	Amt_1	Date_2	Amt_2
12345678	10-Sep-15		01-Sep-15
5678910	05-Sep-15		08-Sep-15
2345678	12-Sep-15		01-Aug-15
1108901	01-Oct-15		10-Oct-15

Thanks in advance.

Re: Report in specific format Required

sunny_talwar — Sun, 01 Nov 2015 14:24:04 GMT

I am not able to follow the logic you are trying to implement? We cannot do random things in QlikView,I would need to know how would you decide which Date is Date_1 and which one is Date_2.