Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

rayapativinod
New Contributor II

For Loop In Qlikview

Hi guys...

greeting of the day.

                         

                            I have an excel file as below.

Group AGroup BMonth
Company A  Company BJan
Company B  Company FFeb
Company C  Company DMar
Company D  Company DApr
Company F   Company Dmay

 

So hear the logic is in the month of  Jan Company A has been taken by Company B,

                                in the month of  Feb Company B has been Taken by Company F,

                                in the month of  Mar Company C has been taken by Company D,

                                in  The month of May company F also taken by Company F

my resultant table in qlik will be like below.

Group AJanFebMarAprMay
Company ACompany BCompany FCompany FCompany FCompany D
Company BCompany BCompany F
Company F
Company FCompany D
Company CCompany CCompany CCompany DCompany DCompany D
Company DCompany DCompany DCompany D
Company D
Company D
Company FCompany FCompany FCompany F
Company F
Company D

Plz help me how to achieve this in qlikview.

Thanks in advance

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: For Loop In Qlikview

May be this

Table:

LOAD GroupA,

GroupB,

Month(Date#(Month, 'MMM')) as Month;

LOAD * INLINE [

    GroupA, GroupB, Month

    Company A,  Company B, Jan

    Company B,  Company F, Feb

    Company C,  Company D, Mar

    Company D,  Company D, Apr

    Company F,    Company D, may

];

FinalTable:

LOAD Distinct GroupA,

If(Num(Month) = 1, GroupB, GroupA) as 1,

If(Num(Month) = 1, GroupB, GroupA) as Jan,

Month,

GroupB

Resident Table;

FOR i = 1 to FieldValueCount('Month')-1

LET vField = FieldValue('Month', $(i)+1);

LET j = $(i) + 1;

Left Join (FinalTable)

LOAD GroupA as $(i),

If(Num(Month) <= $(j), GroupB, [$(i)]) as $(j),

If(Num(Month) <= $(j), GroupB, [$(i)]) as $(vField)

Resident FinalTable;

NEXT i

DROP Table Table;

Capture.PNG

4 Replies

Re: For Loop In Qlikview

You have to create the generic load for this try generic load for this.

Sample Generic Load Script

MVP
MVP

Re: For Loop In Qlikview

May be this

Table:

LOAD GroupA,

GroupB,

Month(Date#(Month, 'MMM')) as Month;

LOAD * INLINE [

    GroupA, GroupB, Month

    Company A,  Company B, Jan

    Company B,  Company F, Feb

    Company C,  Company D, Mar

    Company D,  Company D, Apr

    Company F,    Company D, may

];

FinalTable:

LOAD Distinct GroupA,

If(Num(Month) = 1, GroupB, GroupA) as 1,

If(Num(Month) = 1, GroupB, GroupA) as Jan,

Month,

GroupB

Resident Table;

FOR i = 1 to FieldValueCount('Month')-1

LET vField = FieldValue('Month', $(i)+1);

LET j = $(i) + 1;

Left Join (FinalTable)

LOAD GroupA as $(i),

If(Num(Month) <= $(j), GroupB, [$(i)]) as $(j),

If(Num(Month) <= $(j), GroupB, [$(i)]) as $(vField)

Resident FinalTable;

NEXT i

DROP Table Table;

Capture.PNG

rayapativinod
New Contributor II

Re: For Loop In Qlikview

superb..... Thank you So much Sunny talwar ,    you are genius.......

MVP
MVP

Re: For Loop In Qlikview

I am glad I was able to help. I would suggest you to close this thread if you got what you were looking to get by marking correct and helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny