topic Re: Straight Table query in QlikView

Straight Table query

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i have a qvd which have data for yesterday date and one month old date. in the straight table, i want to capture new items which are added from one month old date. In the script, i have loaded data from both the dates. and in the straight table,

Dimensions:

Item Name

Calculated Dimension as If (Date = '$(vlastmonthdate) and isnull([Item Name]), null(), [Item Name]

Expression:

only({<Daterecord={'$(=vtoday)'}>}[Item Name])

but i am not getting desired results?

please advise

thanks in advance

Sukhwant

Re: Straight Table query

Anonymous — Thu, 15 Sep 2016 09:03:52 GMT

Hi, could you add one example of your app and expected results? Thanks!

Re: Straight Table query

Thu, 15 Sep 2016 09:22:55 GMT

data in QVD
Date	Item Name	Sales
9/15/2016	abc	10
9/15/2016	bcd	20
9/15/2016	cde	30
9/15/2016	efg	40
9/15/2016	fgh	5060
9/15/2016	ghi	50
8/15/2016	abc	20
8/15/2016	bcd	300500
8/15/2016	cde	230

Expected Result in Straight table
Date	Item Name	Sales
9/15/2016	efg	40
9/15/2016	fgh	5060
9/15/2016	ghi	50

Re: Straight Table query

sunny_talwar — Thu, 15 Sep 2016 09:30:06 GMT

May be this:

=Sum({<[Item Name] = {"=Max(Date) = Max(TOTAL Date) and Count(DISTINCT Date) = 1"}>}Sales)

Re: Straight Table query

Thu, 15 Sep 2016 10:04:26 GMT

thanks Sunny.

So for Example sake, i used 2 dates and thus i am using variables in Expression. in reality i have all 20 odd working dates data along with years data. will the above work in this case as well?

thanks

Re: Straight Table query

sunny_talwar — Thu, 15 Sep 2016 10:07:07 GMT

So when you have multiple dates, would you only want to see an item which never showed up before this month?

Re: Straight Table query

Thu, 15 Sep 2016 10:20:39 GMT

yes . as mentioned in the original question, i want to only show new addition of items between today and last month

Re: Straight Table query

sunny_talwar — Thu, 15 Sep 2016 10:24:38 GMT

May be try what I have above or try this:

=Sum({<[Item Name] = {"=Max(Date) = Max(TOTAL Date) and Count(DISTINCT {<Date = {'>=$(=Date(Max(Date, 2)))'}>} Date) = 1"}>}Sales)

Make sure that you date field in the script is created like this if you use the above expression

LOAD Date(Date) as Date,

...

FROM ...