Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
qlikdash
Valued Contributor

My table is getting dropped. What is wrong in the script

I have the following script. what i am trying to achieve is first add a field with max date and then load only those lines that have a max date. but all fields and tables keep getting dropped. Dont know what is the problem.

temp1:

LOAD User,

     [Cell Phone Plan],

     [Start Date],

     [End Date]

FROM

Excel;

temp2:

left join(temp1)

load User,

     max(date([End Date])) as max_end_dt

    

     Resident temp1

     Group by User;

    

Final:

load User,

     [Cell Phone Plan],

     [Start Date],

     [End Date],

     max_end_dt

    

     Resident temp1

     where [End Date]=max_end_dt;

     drop table temp1;

1 Solution

Accepted Solutions
Employee
Employee

Re: My table is getting dropped. What is wrong in the script

Try using noconcatenate to prevent auto-concatenation (because temp1 has the same number and named fields as final) :

noconcatenate

Final:

load User,

     [Cell Phone Plan],

     [Start Date],

     [End Date],

     max_end_dt

   

     Resident temp1

     where [End Date]=max_end_dt;

drop table temp1;

4 Replies
Employee
Employee

Re: My table is getting dropped. What is wrong in the script

Try using noconcatenate to prevent auto-concatenation (because temp1 has the same number and named fields as final) :

noconcatenate

Final:

load User,

     [Cell Phone Plan],

     [Start Date],

     [End Date],

     max_end_dt

   

     Resident temp1

     where [End Date]=max_end_dt;

drop table temp1;

MVP
MVP

Re: My table is getting dropped. What is wrong in the script

How about:

LOAD

    User,

    [Cell Phone Plan],

    Min([Start Date]) As StartDate,

    Max([End Date]) As EndDate

FROM

Excel

Group By

    User,[Cell Phone Plan]

;

ramoncova06
Valued Contributor III

Re: My table is getting dropped. What is wrong in the script

the issue is that since your two tables have the same fields qlikview applies a natural concatenation, using prefix NoConcatenate to your final table could correct this another option is to use a map instead of a left join

temp1:

LOAD User,

     [Cell Phone Plan],

     [Start Date],

     [End Date]

FROM

Excel;

mapping

mapmaxdate:

load User,

     max(date([End Date])) as max_end_dt

   

     Resident temp1

     Group by User;

   

Final:

load User,

     [Cell Phone Plan],

     [Start Date],

     [End Date],

     ('mapmaxdate',User) as max_end_dt

     Resident temp1

     where applymap('mapmaxdate',User,0) <> 0 ;

     drop table temp1;

qlikdash
Valued Contributor

Re: My table is getting dropped. What is wrong in the script

Thanks everyone. Jonathan your soln worked perfectly. awesome