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

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

Anonymous — Wed, 20 May 2015 17:17:36 GMT

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;

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

JonnyPoole — Wed, 20 May 2015 17:23:31 GMT

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;

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

petter — Wed, 20 May 2015 17:29:45 GMT

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]

;

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

ramoncova06 — Wed, 20 May 2015 17:36:52 GMT

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;

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

Anonymous — Wed, 20 May 2015 19:16:30 GMT

Thanks everyone. Jonathan your soln worked perfectly. awesome