Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello there!
I have a problem with formulas in Qlik Sense
Need to count all cases in which difference between two dates is less or equal to set period
Trying this expression:
Count({<([DateA]-[DateB])={"<=13"}>} [DateA])
and have "Error in set modifier expression"
What's wrong with it?
Thanks!
New question:)
One colomn has numbers of unique transactions formatted like:
xz123456789ys, where xz,ys - letters
I need to find out transactions which start with letters ZX and finish with letters SD
like
ZX144278123SD
ZX789654222SD etc
If colomn name is ID what formula should be?
If( Left (ID,2) ='ZX' and Right(ID,2) ='SD', 1,0) as IDFlag
or
If ( ID Like 'ZX*SD', 1,0) as IDFlag
or as calculated dimension:
=If( Left (ID,2) ='ZX' and Right(ID,2) ='SD', ID)
etc.
or using set analysis:
Count({<ID = {"ZX*SD"}>} Distinct ID)
Thanks!
And what if i need to separate all IDs into 3 groups:
1. 'ZX*SD'+'ZX*FG'
2. 'LK*SD'
3. others
I add to script:
If ( ID Like 'ZX*SD' and Like 'ZX*FG', 1,0) as ZX
If ( ID Like 'LK*SD', 1,0) as LK
and how to group rest of IDs to make 3rd group?
Something like:
If ( ID NotLike 'ZX*FG' and NotLike 'ZX*SD' and NotLike 'LK*SD', 1,0) as Rest
Another step is to add a filter to worksheet to switch between this groups
Now I can add separate filters for ZX and LK and choose between 1 and 0 to count or not count such strings
I'd like to have 1 filter that allows turning on/off all of 3 groups ( ZX,LK,Rest)
I guess I need smth like setting (ZX,LK,Rest) as TypeOfOperation and adding filter TypeOfOperation
Another question
I need to count number of strings which have equal text in 2 different colomns
This seems very easy, I try:
Count({<Colomn1={"=Colomn2"}>} Colomn1)
Count({<Colomn1={"=[Colomn2]"}>} Colomn1)
Count({<Colomn1={"=[Colomn2]"}>} [Colomn1])
and none of it work
So, I managed to fix database to have 3 groups for 1st question
Still cannot find strings with equal colomns
I have one file showing Region and fact Sorting Center (SC) for real transactions
1.NY - SC1
2.NY - SC3
3.LA - SC2
4.MA - SC1
5.WY - SC1
6.MA - SC2
Another file has Regions and Target Sorting Centers:
NY - SC1
LA - SC1
MA - SC1
WY - SC3
I need to separate strings which were presorted to right (Target) sorting center
In my example strings 1,4 were presorted right
I tried to put it in script, like that:
FactSC&'='&TargetSC as RightPresorting
FactSC&'<>&TargetSC as WrongPresorting
But FactSC and TargetSC are loaded from different sources and QlikSence shows mistake
Another way is to make formula for pivot table, I tried
Count({$<TARGETSC={"=[FactSC]}>} [TARGETSC]) which doesnt work
Any thoughts on right formula/ approach?
You can MAP your targets to your facts and then perform the check:
MAP:
MAPPING LOAD
Region, [Target Sorting Center]
FROM 'AnotherFile';
LOAD *,
if([Sorting Center] <> Target, 'Wrong','Right') as Presorted;
LOAD
Region,
[Sorting Center],
Applymap('MAP',Region,'no mapping') as Target
FROM 'OneFile';
I tried to modify script several times and still no success
In fact my task is a bit more complicated than I stated in 1st message
1st file has 1 mln strings with factSC full name and destination index (the task is about logistics)
2nd file has correspondence between factSC full name and factSC
3rd file has correspondence between destination index and destination region
4th file has correspondence between region and targetSC
I need to find out strings which were prosorted right, factSC=targetSC
What I try is:
MAP:
MAPPING LOAD
factSC full name, index
FROM 1st file;
MAPPING LOAD
factSC full name, factSC
FROM 2nd file;
MAPPING LOAD
index, region
FROM 3rd file;
MAPPING LOAD
Region, targetSC
FROM 4th file;
LOAD *,
if([factSC] <> targetSC 'Wrong','Right') as Presorted;
Applymap('MAP',Region,'no mapping') as Target
I wonder if I can make 4 MAPPING LOADs
What is Applymap for?
What source I put after it ( it has data from different files (2nd and 4th)?