Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data but some are missing codes. I have tried Peek() and it has worked but not perfectly. Thanks in advance!
If Code is "NoCode" then take the Code above it with the same City, else take the max code or peek code (i'm not sure which one) for this specific row with the same City.
Data Sample:
LOAD*Inline [
City, Town, Code
City1, Town1, 1000,
City1, Town2, 2000,
City1, Town3, NoCode,
City2, Town1, 1001,
City2, Town2, NoCode,
City2, Town3, NoCode,
City1, Town4, NoCode,
City1, Town5, NoCode
]
May be try this
DataSample: LOAD * INLINE [ City, Town, Code City1, Town1, 1000 City1, Town2, 2000 City1, Town3, NoCode City2, Town1, 1001 City2, Town2, NoCode City2, Town3, NoCode City1, Town4, NoCode City1, Town5, NoCode ]; FinalTable: NoConcatenate LOAD City, Town, If(City = Previous(City), If(Code = 'NoCode', Peek('Code'), Code), Code) as Code Resident DataSample Order By City, Town; DROP Table DataSample;
To get this
Hi Sunny,
Thanks for your prompt reply as always. After applying your example, I found out that a lot of my first rows were also empty (see example). I would like to give City1, Town1 the max Code possible with the same City, in this case that would be 2000.
DataSample:
LOAD * INLINE [
City, Town, Code
City1, Town1, NoCode <--------
City1, Town2, 2000
City1, Town3, NoCode
City2, Town1, 1001
City2, Town2, NoCode
City2, Town3, NoCode
City1, Town4, NoCode
City1, Town5, NoCode
];
FinalTable:
NoConcatenate
LOAD City,
Town,
If(City = Previous(City), If(Code = 'NoCode', Peek('Code'), Code), Code) as Code
Resident DataSample
Order By City, Town;
DROP Table DataSample;
Let me ask you this... do you want to apply the max code or previous code? Those two are different things and I want to make sure that I give you a code which works for the issue you are trying to address