topic Re: please help in QlikView

please help

Wed, 11 Dec 2013 07:24:59 GMT

LOAD Category,

Loc,

mon,

tar_qty,

year,

ITPCDE,

ITPFCN,

ITPTCN,

fs1,

fs2,

fs3,

fs4,

fs5,

fs6,

fs7,

fs8,

fs9,

fs10,

fs11,

fs12,

fs13,

fs14,

ts1,

ts2,

ts3,

ts4,

ts5,

ts6,

ts7,

ts8,

ts9,

ts10,

ts11,

ts12,

ts13,

ts14,

FROM

(qvd);

I want to make another column using below part. i make fsize as a global variable.

IF fs1>0 then

fsize= fs1

ELSEIF fs2>0 then

fsize= fs2

.................

Re: please help

Wed, 11 Dec 2013 07:31:54 GMT

Hi,

You want to add one more column or create global variable.

Global variable means, How that value aggregated (max of fs1/fs2 , Sum of fs1/fs2...)

Regards,

Karthik

Re: please help

Wed, 11 Dec 2013 08:09:30 GMT

my problem is i only want to get one fs value from all 14. that s why i use if to select that value

Re: please help

Wed, 11 Dec 2013 08:11:05 GMT

how i can select one fs value among 14 and make a column of it?

Re: please help

Anonymous — Wed, 11 Dec 2013 08:19:19 GMT

You can use nested if conditions and get a single value as

load

Category,

Loc,

mon,

tar_qty,

year,

ITPCDE,

ITPFCN,

ITPTCN,

if(fs1>0,fs1,if(fs2>0,fs2,if(fs3>0,fs3...........))) as fsize from....;

now crate a variable

let vFsize=peek('fsize',-1);

Regards

Nitin

Re: please help

salto — Wed, 11 Dec 2013 08:23:25 GMT

Hi,

please load all values into a resident table and then write an if to read them again from the resident table, selecting only the fs value you need. Then drop the resident table:

Temp:

LOAD Category,

Loc,

mon,

tar_qty,

year,

ITPCDE,

ITPFCN,

ITPTCN,

fs1,

fs2,

fs3,

fs4,

fs5,

fs6,

fs7,

fs8,

fs9,

fs10,

fs11,

fs12,

fs13,

fs14,

ts1,

ts2,

ts3,

ts4,

ts5,

ts6,

ts7,

ts8,

ts9,

ts10,

ts11,

ts12,

ts13,

ts14,

FROM

(qvd);

Data:

Load

Loc,

mon,

tar_qty,

year,

ITPCDE,

ITPFCN,

ITPTCN,

if(fs1>0, fs1,

if(fs2>0, fs2,

if(fs3>0, fs3,

if(fs4>0, fs4,

if(fs5>0, fs5,

if(fs6>0, fs6,

if(fs7>0, fs7,

if(fs8>0, fs8,

if(fs9>0, fs9,

if(fs10>0, fs10,

if(fs11>0, fs11,

if(fs12>0, fs12,

if(fs13>0, fs13,

if(fs14>0, fs14,0)))))))))))))) as fsize,

ts1,

ts2,

ts3,

ts4,

ts5,

ts6,

ts7,

ts8,

ts9,

ts10,

ts11,

ts12,

ts13,

ts14 resident Temp;

drop table Temp;

Hope this helps.

Re: please help

Wed, 11 Dec 2013 08:24:28 GMT

nice, I ll try. thanks

Re: please help

Wed, 11 Dec 2013 08:35:25 GMT

thanks, i ll try

Re: please help

Wed, 11 Dec 2013 08:51:46 GMT

thanks all of u . it s really a big help. now i have another problem. I want to get these data to current date. i have TRA_DATE in my currentstock.qvd. pls help again

Re: please help

salto — Wed, 11 Dec 2013 08:56:13 GMT

Hi Dushan,

I'm afraid I do not understand. Could you please explain the details?

Thanks!

Re: please help

Wed, 11 Dec 2013 09:58:47 GMT

hi SALTO,

i have to select TRA_DATE= currentdate because i want the data to today

LOAD

TRA_LOC as Loc,

TRA_DATE,

SUM(TRA_QTY) as Total,

SUM(TRA_QTY1) as size1,

SUM(TRA_QTY2) as size2,

SUM(TRA_QTY3) as size3,

SUM(TRA_QTY4) as size4,

SUM(TRA_QTY5) as size5,

SUM(TRA_QTY6) as size6,

SUM(TRA_QTY7) as size7,

SUM(TRA_QTY8) as size8,

SUM(TRA_QTY9) as size9,

SUM(TRA_QTY10) as size10,

SUM(TRA_QTY11) as size11,

SUM(TRA_QTY12) as size12,

SUM(TRA_QTY13) as size13,

SUM(TRA_QTY14) as size14,

TRA_CAT as Category

FROM

Re: please help

Anonymous — Wed, 11 Dec 2013 10:01:57 GMT

you can provide a where condition as

date(Tran_date)=today();

or if you dont want to filter out the data, then

add a trigger on a sheet to select the current date as

Add Action> Select in Field>> tran_date,>>

today()

Re: please help

salto — Wed, 11 Dec 2013 10:06:08 GMT

Hi,

you need to set up a Master Calendar on which one of the fields is DateID, with all dates from 48 months: before for example:

Let StartDate = num(date(YearStart(AddMonths(Today(),-48,0)),'YYYYMMDD'));

Let EndDate = num(date(YearEnd(AddMonths(Today(),0,0)),'YYYYMMDD'));

Calendar:

Load *,

Year(Date) AS [Year],

Month(Date) AS [Month],

num(Month(Date)) AS [MonthNum],

Day(Date) AS [Day],

Year(Date)&num(Month(Date),00) as [Year Month],

Load Date($(StartDate)+(Iterno()-1),'YYYYMMDD') as Date,

Date($(StartDate)+(Iterno()-1),'YYYYMMDD') as DateID

Autogenerate 1 While Date($(StartDate)+(Iterno()-1)) <=Date($(EndDate)

Then load the second table:

Data:

LOAD

TRA_LOC as Loc,

TRA_DATE as DateID

SUM(TRA_QTY) as Total,

SUM(TRA_QTY1) as size1,

SUM(TRA_QTY2) as size2,

SUM(TRA_QTY3) as size3,

SUM(TRA_QTY4) as size4,

SUM(TRA_QTY5) as size5,

SUM(TRA_QTY6) as size6,

SUM(TRA_QTY7) as size7,

SUM(TRA_QTY8) as size8,

SUM(TRA_QTY9) as size9,

SUM(TRA_QTY10) as size10,

SUM(TRA_QTY11) as size11,

SUM(TRA_QTY12) as size12,

SUM(TRA_QTY13) as size13,

SUM(TRA_QTY14) as size14,

TRA_CAT as Category

FROM

Hope this helps.

Re: please help

Wed, 11 Dec 2013 10:09:13 GMT

ok i ll try. thanks

Re: please help

Wed, 11 Dec 2013 10:24:42 GMT

thanks for ur help